Re: Date function in DB2 V8.1 for Z/OS

Lennart wrote:
> On May 6, 10:56 pm, JAW <jwill...@[EMAIL PROTECTED]
> wrote:
>> I need to get the first day of the previous month in a query.
>>
>> I thought CURRENT DATE and DAYOFMONTH would allow me to da it but it
>> is noe
>>
>> WHERE DTXL >= 1st day of previous month
>>
>> Anyone got a good idea?
>
> I'm not familiar with DB2 for Z/OS but do you mean something like:
>
> [lelle@[EMAIL PROTECTED]
 db2 "values current_date - 1 month -
> day(current_date) days + 1 day"
>
> 1
> ----------
> 04/01/2008
>
>  1 record(s) selected.
>
> /Lennart

I'm not sure your solution will work in all cases.
How about current_date values like:
05/31/2008
or
03/29/2008 vs. 03/30/2008 vs. 03/31/2008

I think it is more safe to first calculate the first day of the current 
month and subtract a month afterwards:

SELECT (CURRENT DATE - (DAYOFMONTH(CURRENT DATE) - 1) DAYS) - 1 MONTH
FROM SYSIBM.SYSDUMMY1

Cheers!

-- 
Jeroen