Here is a simple DB2 SQL statement which returns the last date (MM/DD/CCYY) of a month by specifying any date within the month:
SELECT (DATE('02/01/2000') + 1 MONTH) - DAY(DATE('02/01/2000')) DAYS FROM "SYSIBM".SYSDUMMY1
Results from the above example:
Wanglong Y. writes: I found the following to be a simpler version of this tip that achieves exactly the same thing. Use the *first* day of the *next* month in the SQL to get the date of the *last* day of the desired month:
SELECT DATE('03/01/2002') - (1 DAY) FROM SomeDummyTable
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best DB2 Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL or DB2 questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL and DB2 gurus are waiting to answer your technical questions.