SEO

#1 (**permalink**) 02-26-2008, 03:56 PM

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.

#2 (**permalink**) 02-26-2008, 03:56 PM

Stephan,

> are this actually valid expressions for DB2 v 7.2?
are you testing us or the database?

Please state result.

Regards Rolf

#3 (**permalink**) 02-26-2008, 03:56 PM

"Stefan Bauer" <stefanbauer24@yahoo.de> wrote in message
news:64aae679.0311030510.672ba308@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.

#4 (**permalink**) 02-26-2008, 03:56 PM

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.

#5 (**permalink**) 02-26-2008, 03:56 PM

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 <badamache@2muchspam.yahoo.com> wrote in message news:<bo5rhp$s26$1@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.

#6 (**permalink**) 02-26-2008, 03:57 PM

stefanbauer24@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.

#7 (**permalink**) 02-26-2008, 03:58 PM

Thank you! :-)

:-)

S.B.

tonkuma@jp.ibm.com (Tokunaga T.) wrote in message news:<8156d9ae.0311041559.6b4a17d@posting.google.c om>...
> stefanbauer24@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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

DATE: valid expression