HOWTO Get the last day of the month with SQL
30 June 2006Have you ever needed to find out what the last day of the month was with SQL. Lets say you needed to round up a date to the last day of the month… I found this necessary when writing a HR Benefits extract… They needed the benefits end date to be the last day of the month, so there was no lapse in medical coverage.
This will get you the actual day number
SELECT DAY(DATEADD(DAY,-1, DATEADD(MONTH,1, DATEADD(DAY,1-DAY(@d),@d)))) AS 'Last day of the month'
If you want the full date just remove the outer DAY function
see http://www.extremeexperts.com/SQL/Tips/DateTrick.aspx for more ways to do it
Leave a Reply
You must be logged in to post a comment.
One Response to “HOWTO Get the last day of the month with SQL”
October 3rd, 2006 at 8:09 am
[...] Ok so now you know how to get the last day of the month with SQL, but how can you get the first day or the last day of the month given a specific date? That problem is so simple with dot net and csharp! There is a convenience constructor for the DateTime object that can help us with it. This will even work with the computers local date time, or localizations. [...]