First and last day of the current month T-SQL

In programming there are usually many ways to accomplish a given task. A good developer is always on the search for a cleaner algorithm. The caveat being that less code is great as long as it does not come at the expense of code readability/maintainability.

One algorithm question I used to ask in interviews went as follows:

Give me an algorithm to calculate the last day of the current month assuming that your language has functions to both create dates and perform date arithmetic.

A simple way to do this is to find the first day of the next month, and subtract a day. T-SQL does not have a last day of the month function so I found that I was having to write code like this a lot:

 
declare @startDate dateTime
declare @endDate dateTime
set @startDate = getDate()
set @startDate = CAST(CAST(YEAR(@startDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@startDate) AS VARCHAR(2)) + '/01' AS DATETIME)
set @endDate = DATEADD(month, +1, @startDate)
set @endDate = DATEADD(day, -1, @endDate)

print @startDate
print @endDate

But in reading Itzik Ben-Gan's book T-SQL Fundamentals, he illustrated a simpler way to to this:

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = DATEADD(month, DATEDIFF(month,'20120101', CURRENT_TIMESTAMP), '20120101')
SET @EndDate = DATEADD(month, DATEDIFF(month,'20120131', CURRENT_TIMESTAMP), '20120131')
print @startDate
print @EndDate 

This way simply calculates the month offset between an anchor date and the current date and then adds that many months back to the anchor date. If the anchor date is the first date of its month, this yields the first date of the current month. However if the anchor date is the last day of its month, you get the last day of the current month.

For me a much more elegant algorithm that is readable and maintainable.