HOWTO Get the last day of the month with SQL
By Tony - Last updated: Friday, June 30, 2006 - Save & Share - One Comment
Have 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
Posted in SQL • • Top Of Page
One Response to “HOWTO Get the last day of the month with SQL”
Write a comment
You need to login to post comments!
Pingback from Getting the First and Last day of the month with .NET C# – Enterprise Software Development
Time October 3, 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. [...]