Get started Bring yourself up to speed with our introductory content.

SQL to select last day of month

Find the last date of a month with this DB2 tip.

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:


Reader Feedback

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

More on this topic


Dig Deeper on iSeries SQL commands and statements