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

One of the details I left out in my last blog was how to get the first day of the month. There are several ways to skin this cat, as with finding the last day of the month. If you take the code from the last blog, and a simple tweak will get this result. You simply need to remove the “-1”, since your looking for the first day of the month, you don’t need to subtract a day.

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

However if you need to find the first day of the previous month you’d tweak it in the datediff statement by replacing the 0 with 31. As you remember 0 represents 1/1/1900, so 31 represents 2/1/1900. The key is when the datediff is calculated on the month, it falls one month short, which translates to the first day of the previous month.

select dateadd(month,datediff(month, 31, getdate()), 0)


Now we can tweak the code from the last blog so we can find both the first and last day of the month.



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