More on date ranges
time to read 4 min | 696 words

Like I said, I didn't really like my solution to date ranges using Common Table Expression and recursion. It is very limiting in many ways, performance not the least of them.

So I set out to find a better way, and found it with table valued functions, like this:

ALTER FUNCTION DateRange

(    

      @start datetime,

      @end datetime

)

RETURNS @DateRange TABLE

(

      CurrentDate datetime

)

AS

BEGIN

      WHILE (@start < @end)

      BEGIN

            INSERT INTO @DateRange(CurrentDate)

            VALUES(@start)

            SELECT @start = DATEADD(day,1,@start)

      END

      RETURN

END

Like the previous function, this take two date ranges, and return a set of all the dates between them, useful for joining against in many cases.

This function, however, doesn't require you to do anything special (like increasing the recursion depth) and it quite performant.

Again, on a highly loaded server, this function gave me a set of all the dates between 2000 and 2300 in 15 seconds (over 100,000 results!), at the same time that the previous function was still struggling with merely 10 years difference (3,000 results).

Tweet Share Share 0 comments
Tags: