Generating A List Of Dates Out Of Numbers

This post is meant partly as a “reminder to self” but also to show that, if you need to generate a list of dates, all you need are numbers.  Oh well, if that doesn’t make sense: read on!

One of the undocumented tables of SQL Server is called spt_values and it is located in the master database.  This table serves as a lookup table for SQL Server itself, more precisely it’s used by several system stored procedures.

Being a lookup table, it doesn’t consist of many columns.  One of the most important columns though is called type.  This one defines the meaning of the record.  An interesting value for this column is “LNG”.  When filtering on “LNG” it returns the following:

I’m quite sure that SharePoint developers recognize these values stored in the number column.  That’s the locale identifier of the language mentioned in the name column.  Could be interesting in case you need a list of languages to be shown somewhere, in a report parameter for instance.

But anyway, these records are not the point of this blog post so let’s move on.  The title of this post says we’re going to generate some dates out of numbers.  So to get started, what we need are those numbers.  Guess what the spt_values table contains?  Indeed, when filtering the name column on “P”, what we get are numbers starting at zero and ending at 2047:

And how can we turn these into dates?  Using some creativity it’s not that complicated.  Let’s say we want to generate a list of dates starting at the first of January of the current year up until today, could be interesting for YTD calculations.  Following query does just that:

select CAST(DATEADD(dd, -number, GETDATE()) as date) dt
from master..spt_values
where type = 'P' and number < DATEPART(dy, GETDATE())

As you can see, several datetime-related functions are being used here.  The easiest one is GETDATE() which returns the current system timestamp of the database server.

Then we’ve got DATEPART() in combination with the “dy” parameter.  This returns the numeric value for the day of the year that the date parameter represents.  The following query returns 336 when ran today (December 2, 2009):

select DATEPART(dy, GETDATE())

(For readability purposes I will not repeat “when ran today” each time, the remainder of the post assumes everything is being executed on the aforementioned date.)

These two functions are being used in the WHERE clause.   So what the WHERE clause says is that we want all numbers smaller than 336.

Okay, so let’s move on to the SELECT.  It uses the DATEADD() function to subtract the retrieved number values from the current system date.  How does that work?  In combination with the “dd” parameter it tells the function what we’re working on are the days, and the minus sign in front of the number field ensures that days are being subtracted instead of added.

And to finish off, the value returned by DATEADD() is being converted to the date type using the CAST() function.  This removes the time value of the datetime type which is good because all we needed are dates.

This is what the query returns:

As you can see, we get 336 records which matches exactly with the values returned by DATEPART(dy, GETDATE()).  (BTW: the number field starts at zero which is why we get 336 instead of 335 records here)

For more advanced usage of a list of numbers I’d like to point you the following article by colleague Experts Exchange Expert Mark Wills: Fun with MS SQL spt_values for delimited strings and virtual calendars

Have fun!

Valentino.