Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    18

    Arrow Unanswered: DATE: valid expression

    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.

  2. #2
    Join Date
    Feb 2003
    Posts
    9

    Re: DATE: valid expression

    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

    Originally posted by stefanB
    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.

  3. #3
    Join Date
    Oct 2003
    Posts
    18

    Re: DATE: valid expression

    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.

    Originally posted by chimes1967
    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

  4. #4
    Join Date
    Feb 2003
    Posts
    9

    Re: DATE: valid expression

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

    Originally posted by stefanB
    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.

  5. #5
    Join Date
    Oct 2003
    Posts
    18

    Re: DATE: valid expression

    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.

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

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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

  7. #7
    Join Date
    Oct 2003
    Posts
    18
    Hi,

    yes, but how do I get the first day of the following month?
    Thanks in Advance,
    S.B.
    Originally posted by Marcus_A
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •