Hi all,

Is this actually a valid expression for DB v 7.2?

SELECT TABLE.ATTR1
FROM TABLE
WHERE
CURRENT DATE - 1 MONTH <= LAST_DAY(TABLE.ATTR2)

ATTR2 is a DATE-column...

Appreciate everyones help!!!

Thanks in Advance,

S.B.

CURRENT DATE - 1 MONTH <= (attr2 + 1 MONTH) - day(attr2 + 1 month) days
works, last_day is an oracle function, not a db2. The above should do the same thing though.


Cliff

Hi,

thanks! That's it!

By the way... Are this two alternative expressions valid?

CURRENT DATE - 1 MONTH <= (attr2 + 1 MONTH) - day(attr2) days

CURRENT DATE - 1 MONTH <= (attr2 + 1 MONTH) - (1 DAY)


Thanks in Advance,

S.B.

They are valid sql statements, but they will not get you the last day of the month.

Hi,

thanks for you reply!
AFAIK LAST_DAY is a scalar function only for DB2 Universal Database for OS/390 and z/OS.
I found this SQL statement on SearchDatabase, that calculates the last day of a month too:

(DATE('02/01/2000') + 1 MONTH) - DAY(DATE('02/01/2000')) DAYS

with the result "02/29/2000".

Unfortunately, I sill don't have a DB2-account, so I cann't prove, if the statements provide the same result.
The only drifference seems to be "+ 1 MONTH" in the last part of the expression...

S.B.

If you take the first day of the following month that you want and subtract 1 day, then you will get the last day of the month.

For last day of February 2000, take March 01, 2000 and subtract 1 day:

select date('2000-03-01') - 1 day, from employee

yields

02/29/2000

Hi,

yes, but how do I get the first day of the following month?
Thanks in Advance,
S.B.