I am trying to create several reports based on a DB2 database that all run with date ranges. Several of the reports have parameters based on datetimestamp fields (i.e. Where DTS GE Begin_Date and DTS LE End_Date). However, when running for a month, I have to input the first day of the reporting month to the first day of the next month, when what I really want to put in is the first day of the reporting month to the last day of the reporting month. I assumed the LE command would take care of that, but when I use the first to last days, it does not pick up date from the last day of the month.

When I use the same code against just a date field, it works correctly. Can someone explain why it is doing this and maybe what I can do to remedy it?

Here is the generic where clause I use:
WHERE ( DATE_TIME GE DT(&START_DATE) ) AND ( DATE_TIME LE DT(&END_DATE) );

This message has been edited. Last edited by: MO Admin,

Are you using the full date-time stamp or just the date portion? My guess is that the LE is checking against the time of 00:00:00.00000 which would be the first millisecond of the last day (and thus eliminating any further timstamps for the last day) when it actually should be checking against 23:59:59.99999 - the last one of the last day. Otherwise, check to make sure that your comparision is only against the date-portion of the timestamp. Not sure how that is translated to SQL for DB2.

Darin beat me to the post, but...

If you are using the DT function without supplying the time portion, then it is defaulting to 00:00:00. If your DATE-TIME column has the value 20100430 00:00:01 your WHERE clause will not find that record because the WHERE clause becomes WHERE 20100430.00:00:01 LE 20100430.00:00:00, which fails. To avoid this problem, you could set the time to midnight:

WHERE ( DATE_TIME GE DT(&START_DATE) ) AND ( DATE_TIME LE DT(&END_DATE 23:59:59) );

However, I often find it easier to just use LT the first day of the next month@midnight because I can always calculate the value as month/01/2010 whereas the last day of the month will be 28, 29, 30, or 31, depending on the month and often needs to be an additional date calculation.

Thank you for the quick responses. I added the midnight timestamp to the back of my end date parm in the where clause and that worked. We prefer to use the last day of the month instead of the first day. We always manually put it in, so the difference in number of days for each month is not an issue for us.

Thanks again.