Regd the Date logic

cvijay784 · New User Joined: 18 Jun 2008 Posts: 54 Location: Colombo

Hi,

My current requirement is to extract records between the particular months, i.e to extract the data from staring of the month to end of the some other month..

Example Query:
Select *
from EMP_TABLE
where EMP_DATA BETWEEN :WS-START-DATE AND :WS-END-DATE

Here i am altering the start & end dates according to the current system date..but day fields are hardcoded as '01' and '31' for WS-START-DATE AND WS-END-DATE respectively..

Since 31st Apr is not a valid date ..am getting -181 error for the above mentioned query..

How can i include a logic to overcome my problem instead of hardcoding the day based on the month?

Escapa · Posted: Mon May 18, 2009 1:31 pm

How about using LAST_DAY(DATE) scalar function in DB2?

cvijay784 · New User Joined: 18 Jun 2008 Posts: 54 Location: Colombo

Yes..i think this LAST_DAY function will be useful for my req with little bit modification..

Ex:

Here WS-START-DATE = 2009-04-01

EXEC SQL
SET :WS-END-DATE = LAST_DAY(:WS-START-DATE)
END-EXEC.

Now WS-END-DATE = 2009-04-30

And i think for leap years also it should work to give the proper end-date

Thanks for the reply..

Escapa · Posted: Mon May 18, 2009 2:39 pm

Welcome....