Date function in DB2 V8.1 for Z/OS

[JAW]

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?

[Lennart]

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

On May 6, 10:56 pm, JAW <jwill...@aglre sources.comwrot e:

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@53dbd181]$ db2 "values current_date - 1 month -
day(current_dat e) days + 1 day"

1
----------
04/01/2008

1 record(s) selected.

/Lennart

[The Boss]

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

Lennart wrote:

On May 6, 10:56 pm, JAW <jwill...@aglre sources.comwrot e:

>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@53dbd181]$ db2 "values current_date - 1 month -
day(current_dat e) 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(CUR RENT DATE) - 1) DAYS) - 1 MONTH
FROM SYSIBM.SYSDUMMY 1

Cheers!

--
Jeroen

[Tonkuma]

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

CURRENT DATE - (DAY(CURRENT DATE)-1) DAYS - 1 MONTH
or
CURRENT DATE - (DAYOFMONTH(CUR RENT DATE)-1) DAYS - 1 MONTH

[Tonkuma]

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

It would be better to use DAY instead of DAYOFMONTH for portability.

Article:
The SQL Reference for Cross-Platform Development

http://www-128.ibm.com/developerworks/db2/library/techarticle/0206sqlref/0206sqlref.html

PDF Manual:
SQL Reference for Cross-Platform Development Version 3
(DB2 for: z/OS V9, iSeries V5R4, LUW V9)
ftp://ftp.software.ibm.com/ps/produc...S/cpsqlrv3.pdf