How do I calculate the last day of the month in SQL?

Question

Specifically MSSQL 2005.

Solution 1

Here's a solution that gives you the last second of the current month. You can extract the date part or modify it to return just the day. I tested this on SQL Server 2005.

select dateadd( s, -1, dateadd( mm, datediff( m, 0, getdate() ) + 1, 0 ) );

To understand how it works we have to look at the dateadd() and datediff() functions.

DATEADD(datepart, number, date)  
DATEDIFF(datepart, startdate, enddate)

If you run just the most inner call to datediff(), you get the current month number since timestamp 0.

select datediff(m, 0, getdate() );  
1327

The next part adds that number of months plus 1 to the 0 timestamp, giving you the starting point of the next calendar month.

select dateadd( mm, datediff( m, 0, getdate() ) + 1, 0 );
2010-09-01 00:00:00.000

Finally, the outer dateadd() just subtracts one second from the beginning timestamp of next month, giving you the last second of the current month.

select dateadd( s, -1, dateadd( mm, datediff( m, 0, getdate() ) + 1, 0 ) );
2010-08-31 23:59:59.000

---

This old answer (below) has a bug where it doesn't work on the last day of a month that has more days than the next month. I'm leaving it here as a warning to others.

Add one month to the current date, and then subtract the value returned by the DAY function applied to the current date using the functions DAY and DATEADD.

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

Solution 2

SELECT DATEADD(M, DATEDIFF(M, '1990-01-01T00:00:00.000', CURRENT_TIMESTAMP), '1990-01-31T00:00:00.000')

Explanation:

General approach: use temporal functionality.

SELECT '1990-01-01T00:00:00.000', '1990-01-31T00:00:00.000'

These are DATETIME literals, being the first time granule on the first day and last day respectively of the same 31-day month. Which month is chosen is entirely arbitrary.

SELECT DATEDIFF(M, '1990-01-01T00:00:00.000', CURRENT_TIMESTAMP)

This is the difference in whole months between the first day of the reference month and the current timestamp. Let's call this @calc.

SELECT DATEADD(M, @calc, '1990-01-31T00:00:00.000')

This adds @calc month granules to the last day of the reference month, the result of which is the current timestamp 'rounded' to the last day of its month. Q.E. D.

Solution 3

Try this:

DATEADD (DAY, -1, DATEADD (MONTH, DATEDIFF (MONTH, 0, CURRENT_TIMESTAMP) + 1, 0)

Solution 4

They key points are if you can get first day of current month,Last Day of Last Month and Last Day of Current Month.

Below is the Step by Step way to write query:

In SQL Server Date Starts from 1901/01/01( Date 0) and up to now each month can be identified by a number. Month 12 is first month of 1902 means January. Month 1200 is January of 2001. Similarly each day can be assigned by unique number e.g Date 0 is 1901/01/01. Date 31 is 1901/02/01 as January of 1901 starts from 0.

To find out First day of Current Month(Current Date or a given date)

1. First we need to check how many months have passed since date 0(1901/01/01). SELECT DATEDIFF(MM,0,GETDATE())
2. Add same number of month to date 0(1901/01/01)

   SELECT DATEADD(MM, DATEDIFF(MM,0,GETDATE()),0)

Then we will get first day of current month(Current Date or a given date)

To get Last Day of Last Month

We need to subtract a second from first day of current month

SELECT DATEADD(SS,-1,DATEADD(MM, DATEDIFF(MM,0,GETDATE()),0))

To get Last Day of Current Month

To get first day of current month first we checked how many months have been passed since date 0(1901/01/01). If we add another month with the total months since date 0 and then add total months with date 0, we will get first day of next month.

SELECT DATEADD(MM, DATEDIFF(MM,0,GETDATE())+1,0)

If we get first day of next month then to get last day of current month, all we need to subtract a second.

SELECT DATEADD(SS,-1,DATEADD(MM, DATEDIFF(MM,0,GETDATE())+1,0))

Hope that would help.

Solution 5

Using SQL2005, you do not have access to a helpful function EOMONTH(), So you must calculate this yourself.

This simple function will works similar to EOMONTH

CREATE FUNCTION dbo.endofmonth(@date DATETIME= NULL)
RETURNS DATETIME
BEGIN
RETURN DATEADD(DD, -1, DATEADD(MM, +1, DATEADD(DD, 1 - DATEPART(DD, ISNULL(@date,GETDATE())), ISNULL(@date,GETDATE()))))
END

Query to perform:

SELECT dbo.endofmonth(DEFAULT)  --Current month-end date
SELECT dbo.endofmonth('02/25/2012') --User-defined month-end date

View more solutions

Comments

[Amy B]

What if the two getdate calls give answers in different dates?

[Bill the Lizard]

I suppose that's possible. You'd have to declare a variable to hold today's date beforehand.

[JeffO]

They won't. We create reports that generate year, quarter, month and rundate (GetDate()) columns that take 30 seconds and the value never changes.

[JeffO]

Try: Select GetDate() Select GetDate() and compare to: Select GetDate() GO Select GetDate() The first gives the same time, the second does not.

[MatBailie]

They can't come back differently. Scalar functions are executed once and then their result substituted in. That's why if you do [SELECT RAND(), * FROM myTable] ever row has the same value for rand. Even if the query took a day to execute, the value at the Start of the exceution is what is used.

[MatBailie]

Has this been tested for dates like "30th Jan"? My understanding is that "dateadd(month, 1, getdate())" would give "28th Feb" and then "-day(getdate())" would give "-30". The final result being the "28th Jan", which is wrong...

[MatBailie]

I've often found that people don't follow the logic of this. So, although it seems to be correct (where the answer accepted appears to Not be correct) , I'm not going to give +1. Any chance you can edit the answer to give the explanation?

[LittleBobbyTables - Au Revoir]

Maybe it's just because I'm using SQL 2000, but when I use this query for today's date, the query returns '2010-08-30', when I would have expected '2010-08-31'.

[Bill the Lizard]

@LittleBobbyTables: There was a bug in my old answer. Thanks for commenting on it, as I was unaware of the bug (I guess the other comments were left before we had the notification envelope here on SO). See my update for a better solution.