Date comparison

Grace Chen

Date comparison
Hi list,
We are running DB2 V8 New function Mode for Z/OS. There is a very
strange result on the date comparison. Can anyone there explain this
result?
.
SELECT
FMGOU.BR_NBR,
FMGME.ACCT_NBR,
P20_ACTTYP.SHRT_NM,
FMGAC.SUB_NBR,
FHGAS.END_DT_ID,
FHGAS.BALANCE_LOW_MTH,
FHGAS.BAL_INT_MIN_MTH,
FHGAS.BALANCE,
P20_ACTSTS.SHRT_NM
FROM
FMGOU,
FMGME,
TDWHP0S.FCVCL_ACTTYP P20_ACTTYP,
FMGAC,
FHGAS,
TDWHP0S.FCVCL_ACTSTS P20_ACTSTS
WHERE
( FMGOU.OU_ID=FMGME.OU_ID )
AND ( FMGOU.CU_NBR=FMGME.CU_NBR )
AND ( FMGAC.ACCT_ID=FHGAS.ACCT_ID )
AND ( P20_ACTTYP.CL_ID=FMGAC.ACCT_TYPE_ID )
AND ( FMGAC.ACCT_STATUS_ID=P20_ACTSTS.CL_ID )
AND ( FHGAS.CU_NBR=FMGAC.CU_NBR )
AND ( FHGAS.MRSP_ID=FMGAC.MRSP_ID )
AND ( FMGME.MRSP_ID=FMGAC.MRSP_ID )
AND ( FMGME.CU_NBR=FMGAC.CU_NBR )
AND (
P20_ACTTYP.SHRT_NM IN ('CSIF','CSSP')
AND FHGAS.END_DT_ID BETWEEN '2004-11-30' AND '2005-10-31' )


When I do execute this, I only get results up to '2005-07-31' even
though it should retrieve data beyond that.

When I change the last condition to the following, I get the same
results (dates in the table are only month end dates)

AND FHGAS.END_DT_ID IN ( '2004-11-30', '2004-12-31', '2005-01-31',
'2005-02-28', '2005-03-31', '2005-04-30', '2005-05-31',
'2005-06-30', '2005-07-31', '2005-08-31', '2005-09-30',
'2005-10-31')

When I change the last condition to the following, I only get data
for '2005-08-31'

AND FHGAS.END_DT_ID IN
('2005-08-31', '2005-09-30',
'2005-10-31')

When I change the last condition to the following, I get data for all
three months.

AND FHGAS.END_DT_ID BETWEEN '2005-08-31' AND '2005-10-31'


Thanks,
Grace Chen


This e-mail and any attachments may contain confidential and
privileged information. If you are not the intended recipient,
please notify the sender immediately by return e-mail, delete this
e-mail and destroy any copies. Any dissemination or use of this
information by a person other than the intended recipient is
unauthorized and may be illegal.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dil Pratheek

Re: Date comparison
(in response to Grace Chen)
Hi Grace,

Are you sure you are displaying all the values from your result set?
Are you running this thru SPUFI? Did you try to run this thru TEP2?

Thanks,

Dil Pratheek


Hi list,
We are running DB2 V8 New function Mode for Z/OS. There is a very strange result on the date comparison. Can anyone there explain this result?
.
SELECT
FMGOU.BR_NBR,
FMGME.ACCT_NBR,
P20_ACTTYP.SHRT_NM,
FMGAC.SUB_NBR,
FHGAS.END_DT_ID,
FHGAS.BALANCE_LOW_MTH,
FHGAS.BAL_INT_MIN_MTH,
FHGAS.BALANCE,
P20_ACTSTS.SHRT_NM
FROM
FMGOU,
FMGME,
TDWHP0S.FCVCL_ACTTYP P20_ACTTYP,
FMGAC,
FHGAS,
TDWHP0S.FCVCL_ACTSTS P20_ACTSTS
WHERE
( FMGOU.OU_ID=FMGME.OU_ID )
AND ( FMGOU.CU_NBR=FMGME.CU_NBR )
AND ( FMGAC.ACCT_ID=FHGAS.ACCT_ID )
AND ( P20_ACTTYP.CL_ID=FMGAC.ACCT_TYPE_ID )
AND ( FMGAC.ACCT_STATUS_ID=P20_ACTSTS.CL_ID )
AND ( FHGAS.CU_NBR=FMGAC.CU_NBR )
AND ( FHGAS.MRSP_ID=FMGAC.MRSP_ID )
AND ( FMGME.MRSP_ID=FMGAC.MRSP_ID )
AND ( FMGME.CU_NBR=FMGAC.CU_NBR )
AND (
P20_ACTTYP.SHRT_NM IN ('CSIF','CSSP')
AND FHGAS.END_DT_ID BETWEEN '2004-11-30' AND '2005-10-31' )

When I do execute this, I only get results up to '2005-07-31' even though it should retrieve data beyond that.

When I change the last condition to the following, I get the same results (dates in the table are only month end dates)

AND FHGAS.END_DT_ID IN ( '2004-11-30', '2004-12-31', '2005-01-31',
'2005-02-28', '2005-03-31', '2005-04-30', '2005-05-31',
'2005-06-30', '2005-07-31', '2005-08-31', '2005-09-30',
'2005-10-31')

When I change the last condition to the following, I only get data for '2005-08-31'

AND FHGAS.END_DT_ID IN
('2005-08-31', '2005-09-30',
'2005-10-31')

When I change the last condition to the following, I get data for all three months.

AND FHGAS.END_DT_ID BETWEEN '2005-08-31' AND '2005-10-31'


Thanks,
Grace Chen
--------------------------------------------------------

NOTICE: If received in error, please destroy and notify sender. Sender does not waive confidentiality or privilege, and use is prohibited.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dil Pratheek

Re: Date comparison
(in response to Dil Pratheek)
Hi Grace,

Are you sure you are displaying all the values from your result set?
Are you running this thru SPUFI? Did you try to run this thru TEP2?

Thanks,

Dil Pratheek


Hi list,
We are running DB2 V8 New function Mode for Z/OS. There is a very strange result on the date comparison. Can anyone there explain this result?

SELECT
FMGOU.BR_NBR,
FMGME.ACCT_NBR,
P20_ACTTYP.SHRT_NM,
FMGAC.SUB_NBR,
FHGAS.END_DT_ID,
FHGAS.BALANCE_LOW_MTH,
FHGAS.BAL_INT_MIN_MTH,
FHGAS.BALANCE,
P20_ACTSTS.SHRT_NM
FROM
FMGOU,
FMGME,
TDWHP0S.FCVCL_ACTTYP P20_ACTTYP,
FMGAC,
FHGAS,
TDWHP0S.FCVCL_ACTSTS P20_ACTSTS
WHERE
( FMGOU.OU_ID=FMGME.OU_ID )
AND ( FMGOU.CU_NBR=FMGME.CU_NBR )
AND ( FMGAC.ACCT_ID=FHGAS.ACCT_ID )
AND ( P20_ACTTYP.CL_ID=FMGAC.ACCT_TYPE_ID )
AND ( FMGAC.ACCT_STATUS_ID=P20_ACTSTS.CL_ID )
AND ( FHGAS.CU_NBR=FMGAC.CU_NBR )
AND ( FHGAS.MRSP_ID=FMGAC.MRSP_ID )
AND ( FMGME.MRSP_ID=FMGAC.MRSP_ID )
AND ( FMGME.CU_NBR=FMGAC.CU_NBR )
AND (
P20_ACTTYP.SHRT_NM IN ('CSIF','CSSP')
AND FHGAS.END_DT_ID BETWEEN '2004-11-30' AND '2005-10-31' )

When I do execute this, I only get results up to '2005-07-31' even though it should retrieve data beyond that.

When I change the last condition to the following, I get the same results (dates in the table are only month end dates)

AND FHGAS.END_DT_ID IN ( '2004-11-30', '2004-12-31', '2005-01-31',
'2005-02-28', '2005-03-31', '2005-04-30', '2005-05-31',
'2005-06-30', '2005-07-31', '2005-08-31', '2005-09-30',
'2005-10-31')

When I change the last condition to the following, I only get data for '2005-08-31'

AND FHGAS.END_DT_ID IN
('2005-08-31', '2005-09-30',
'2005-10-31')

When I change the last condition to the following, I get data for all three months.

AND FHGAS.END_DT_ID BETWEEN '2005-08-31' AND '2005-10-31'


Thanks,
Grace Chen
--------------------------------------------------------

NOTICE: If received in error, please destroy and notify sender. Sender does not waive confidentiality or privilege, and use is prohibited.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm