Simple, but effective code example

The other day I answered this question at SQLServerCentral, "Need a date function to return every Wednesday of the year", and got a couple of kudos, one from Jonathan Kehayias and one from Chris Morris (frequent contributor to the SSC forums), so I thought I'd post the code here and then explain it a bit. So here's the code:

SELECT TOP 366
   IDENTITY(INT, 1, 1) AS n
INTO
   #nums
FROM
   sys.all_objects
   
   SELECT 
       DATEADD(DAY, n, '1/1/2008')
   FROM
       #nums
   WHERE
       DATEPART(weekday, DATEADD(DAY, n, '1/1/2008')) = 
              CASE @@DateFirst
                   WHEN 7 THEN 4
                   WHEN 6 THEN 5
                   WHEN 5 THEN 6
                   WHEN 4 THEN 7
                   WHEN 3 THEN 1
                   WHEN 2 THEN 2
                   ELSE 3
              END      
DROP TABLE #nums

The first section just creates a temporary numbers table and you can do that several ways, I just opted for a simple one. If you don't have a numbers table already, you should as there are many good uses for it.

Then I just run a simple select against the temporary numbers table adding each number to the first day of the year. The WHERE clause then just compares the value returned from the DATEPART function (weekday) to the value for Wednesday based on the @@Datefirst value. According to Books on line @@Datefirst:

SET DATEFIRST indicates the specified first day of each week. The U.S. English default is 7, Sunday.

Language settings affect date information.

This can also be affected by using SET DATEFIRST if you want it to be different from the default for the language. This case statement is important if you expect the code to work on any database in any SQL Server install.

Ideally to solve a problem like this you would have a dates table as Jonathan recommends in the thread.

For more information on a numbers table check out these articles:

http://www.sqlservercentral.com/articles/TSQL/62867/
http://www.sqlservercentral.com/articles/Advanced+Querying/2547/