SEO

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?

On May 6, 10:56 pm, JAW <jwill...@aglresources.com> 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@53dbd181]$ db2 "values current_date - 1 month -
day(current_date) days + 1 day"

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

1 record(s) selected.

/Lennart

Lennart wrote:
> On May 6, 10:56 pm, JAW <jwill...@aglresources.com> 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@53dbd181]$ 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

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

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/developerwork...206sqlref.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