Using a recursive common table expression to generate a date range

At one company that I worked for, I inherited a bunch of reporting code written in T-SQL. A lot of it was for reporting on monthly information. Sometimes where were inserts into temp tables for month data and the performance was terrible.

One consultant I hired saw some of the T-SQL code and suggested using a recursive common table expression (CTE) to generate dates. This was much faster than inserting into a temp table.

Here is an example of a CTE to generate the range of days for the current month. Obviously using a recursive algorithm can be dangerous. There is a limit on the number of recursive calls that can be made. You can add OPTION (MAXRECURSION N); to the select from a recursive CTE to limit the number of interations where N is a number between 0 and 32,767.

 
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')

;WITH DateRange(Date) AS
    (
        SELECT
            @StartDate Date
        UNION ALL
        SELECT
            DATEADD(day, 1, Date) Date
        FROM
            DateRange
        WHERE
            Date < @EndDate
    )
SELECT * FROM DateRange

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.