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

Question

Specifically MSSQL 2005.

Answer 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()))

Answer 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.