DATE: valid expression

[Stefan Bauer]

Hi NG,

are this actually valid expressions for DB2 v 7.2?

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

and

SELECT TABLE.ATTR1
FROM TABLE
WHERE
TABLE.ATTR2 <= TABLE.ATTR3

- ATTR2 and ATTR3 are DATE-columns...

Appreciate everyones help!!!

Thanks in Advance,

S.B.

[Rolf Loeben]

Stephan,

are this actually valid expressions for DB2 v 7.2?

are you testing us or the database?

Please state result.

Regards Rolf

[Mark A]

"Stefan Bauer" <st***********@yahoo.de> wrote in message
news:64**************************@posting.google.c om...

Hi NG,

are this actually valid expressions for DB2 v 7.2?

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

and

SELECT TABLE.ATTR1
FROM TABLE
WHERE
TABLE.ATTR2 <= TABLE.ATTR3

- ATTR2 and ATTR3 are DATE-columns...

Appreciate everyones help!!!

Thanks in Advance,

S.B.

I have never heard of last_day function.

The second SQL statement is perfectly legal.

[Blair Adamache]

LAST_DAY is not a function in DB2 on Unix/Linux/Windows as far as we know.

Also, the table should not be called TABLE.

The second query looks okay.

BTW I defined the table as follows to test:
create table table1 (attr1 date, attr2 date, attr3 date)

Stefan Bauer wrote:

Hi NG,

are this actually valid expressions for DB2 v 7.2?

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

and

SELECT TABLE.ATTR1
FROM TABLE
WHERE
TABLE.ATTR2 <= TABLE.ATTR3

- ATTR2 and ATTR3 are DATE-columns...

Appreciate everyones help!!!

Thanks in Advance,

S.B.

[Stefan Bauer]

Hi,

according to the IBM SQL Reference v. 7, LAST_DAY is a scalar
function, that returns a date that represents the last day of the
month, but only for UDB OS/390 and z/OS. :(

I found on the internet some expressions, that seems to provide the
same result. Unfortunately, I don't have a DB2-access, so there is no
possibility for me to prove, if they are correct:

(attr2 + 1 MONTH) - day(attr2 + 1 month) days

(attr2 + 1 MONTH) - day(attr2) days

(attr2 + 1 MONTH) - (1 DAY)

Thanks in Advance,

S.B.

Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<bo**********@hanover.torolab.ibm.com>...

LAST_DAY is not a function in DB2 on Unix/Linux/Windows as far as we know.

Also, the table should not be called TABLE.

The second query looks okay.

BTW I defined the table as follows to test:
create table table1 (attr1 date, attr2 date, attr3 date)

Stefan Bauer wrote:

Hi NG,

are this actually valid expressions for DB2 v 7.2?

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

and

SELECT TABLE.ATTR1
FROM TABLE
WHERE
TABLE.ATTR2 <= TABLE.ATTR3

- ATTR2 and ATTR3 are DATE-columns...

Appreciate everyones help!!!

Thanks in Advance,

S.B.

[Tokunaga T.]

st***********@yahoo.de (Stefan Bauer) wrote in message

Hi,

according to the IBM SQL Reference v. 7, LAST_DAY is a scalar
function, that returns a date that represents the last day of the
month, but only for UDB OS/390 and z/OS. :(

I found on the internet some expressions, that seems to provide the
same result. Unfortunately, I don't have a DB2-access, so there is no
possibility for me to prove, if they are correct:

(attr2 + 1 MONTH) - day(attr2 + 1 month) days Seems OK.

(attr2 + 1 MONTH) - day(attr2) days NG.
If attr2 is 2003-01-30, the expression will return 2003-01-29.

(attr2 + 1 MONTH) - (1 DAY)

NG.

[Stefan Bauer]

Thank you! :-)

:-)

S.B.

to*****@jp.ibm.com (Tokunaga T.) wrote in message news:<81*************************@posting.google.c om>...

st***********@yahoo.de (Stefan Bauer) wrote in message

Hi,

according to the IBM SQL Reference v. 7, LAST_DAY is a scalar
function, that returns a date that represents the last day of the
month, but only for UDB OS/390 and z/OS. :(

I found on the internet some expressions, that seems to provide the
same result. Unfortunately, I don't have a DB2-access, so there is no
possibility for me to prove, if they are correct:

(attr2 + 1 MONTH) - day(attr2 + 1 month) days

Seems OK.

(attr2 + 1 MONTH) - day(attr2) days

NG.
If attr2 is 2003-01-30, the expression will return 2003-01-29.

(attr2 + 1 MONTH) - (1 DAY)

NG.