Get the last day of the month in SQL

Question

I need to get the last day of the month given a date in sql. If I have the first day of the month I can do something like this:

dateadd(day, dateadd(month,'2009-05-01',1), -1)

But does anyone know how to generalize it so I can find the last day of the month for any given date?

Answer 1

Just extend your formula out a little bit:

dateadd(day, -1,
    dateadd(month, 1,
        cast(month('5/15/2009') as varchar(2)) + 
        '/1/' + 
        cast(year('5/15/2009') as varchar(4)))

Answer 2

The LAST_DAY function works nicely for this.

Edit: Actually as mentioned in the comments this probably not for all SQL. I'll leave the post in the possibility of someone looking for a MySQL/Oracle answer stumbling upon it.

From the mysql reference manual:

LAST_DAY(date)

Takes a date or datetime value and returns the corresponding value for the last day of the month. Returns NULL if the argument is invalid.

mysql> SELECT LAST_DAY('2003-02-05');
        -> '2003-02-28'
mysql> SELECT LAST_DAY('2004-02-05');
        -> '2004-02-29'
mysql> SELECT LAST_DAY('2004-01-01 01:01:01');
        -> '2004-01-31'
mysql> SELECT LAST_DAY('2003-03-32');
        -> NULL

LAST_DAY() is available as of MySQL 4.1.1.

Answer 3

Hi

Please refer to this link http://www.sql-server-helper.com/functions/get-last-day-of-month.aspx

I guess that might be of some help.

cheers

Answer 4

You could get the days in the date by using the DAY() function:

dateadd(day, -1, dateadd(month, 1, dateadd(day, 1 - day(date), date)))

Answer 5

Here's my version. No string manipulation or casting required, just one call each to the DATEADD, YEAR and MONTH functions:

DECLARE @test DATETIME
SET @test = GETDATE()  -- or any other date

SELECT DATEADD(month, ((YEAR(@test) - 1900) * 12) + MONTH(@test), -1)

Answer 6

My 2 cents:

select DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(day,(0-(DATEPART(dd,'2008-02-12')-1)),'2008-02-12')))

Raj

Answer 7

If you need it frequently, wrap it in an inline TVF, which is very fast:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/06/21/calculating-third-wednesday-of-the-month-with-inline-udfs.aspx ,