Tags: 2select, attr1from, attr2, database, date, db2, expression, last_day, mysql, oracle, sql, table, tablewherecurrent, valid

DATE: valid expression

On Database » DB2

3,235 words with 6 Comments; publish: Tue, 04 Dec 2007 20:27:00 GMT; (250312.01, « »)

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.

All Comments

Leave a comment...

  • 6 Comments
    • 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

      === Original Words ===

      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.

      #1; Tue, 11 Dec 2007 17:49:00 GMT
    • 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.

      === Original Words ===

      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

      #2; Tue, 11 Dec 2007 17:50:00 GMT
    • They are valid sql statements, but they will not get you the last day of the month.

      === Original Words ===

      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.

      #3; Tue, 11 Dec 2007 17:51:00 GMT
    • 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.

      === Original Words ===

      chimes1967

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

      #4; Tue, 11 Dec 2007 17:53:00 GMT
    • 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

      #5; Tue, 11 Dec 2007 17:53:00 GMT
    • Hi,

      yes, but how do I get the first day of the following month? :)

      Thanks in Advance,

      S.B.=== Original Words ===

      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

      #6; Tue, 11 Dec 2007 17:54:00 GMT