SQL Server Finding Last Day of the Month using DateDiff and DateAdd

Getting the last day of the month is a critical aspect of retrieving data in many circumstances the following scripts are designed to do just that.

This first script figures out the last day of the month for the previous month.

select dateadd(month,datediff(month, 0, getdate()), 0) - 1

To truly understand what’s going on we need to break it down. On the inner function datediff. The syntax for datediff is

DATEDIFF (datepart , startdate , enddate )

However you can as I do below and above is subsititue either the startdate or endate with an integer. This number represents the amount of days since the beginning of time, as SQL Server sees it, or quite simply from 1/1/1900. For example the “one” in the startdate below translates to ‘1/2/1900’, or one day after 1/1/1900. The result function is how many full months have passed between 1/2/1900 and 1/1/2000

select datediff(month, 1, '1/1/2000')

As of writing this on 2/16/09 the following script yeilds a value of 1309. Meaning 1309 months have passed between 2/16/09 and 1/1/1900.

select datediff(month, 0, getdate())

This number is then used with the dateadd function, that again leverages sql servers concept of the beginning of time. Here below using the 1309 result from above I get the first day of the month, by adding 1309 months (retrieved in the datediff) to get the first day of the month.

select dateadd(month, 1309, 0)

Now by subtracting 1 day from the results, I’m able to get the last day of the month.

select dateadd(month, 1309, 0) – 1

Put this all together dynamically and it looks like the first statement.

select dateadd(month,datediff(month, 0, getdate()), 0) – 1


There are other times you might need to retrieve the last date of the month for the previous ten years. The script below does just that.


create table #LastDayHold
(dte datetime)
Declare @Interval int
set @interval = 0
while @interval <>
Begin
Insert into #LastDayHold select dateadd(month,datediff(month, 0, getdate()) - @interval, 0) - 1
Set @interval = @interval + 1
end
select * from #LastDayHold