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:

02/29/2000

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

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

This is all fine and dandy if it's the first day of the month. A better approach, IMO, is:

select datefield - day(datefield) days from file

Test it out:

with f1 as (
select current date as datefield from sysibm.sysdummy1
)
select datefield - day(datefield) days from f1
Cancel

-ADS BY GOOGLE

SearchDataCenter

Close