Wise man or Wise guy? You Decide

Just another WordPress.com weblog

Archive for the ‘Numbers Table’ Category

Simple, but effective code example

Posted by sqlwiseguy on December 23, 2008

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/

Posted in Code Example, Numbers Table, SQL Server, SqlServerCentral | Leave a Comment »