Generate Date Table in SQL Server

From a post of Jeff Moden's that I read (cant find it at the moment). This first creates a temp table of numbers (using Jeff's identity trick), and then creates the table of dates based on that sequential numbers table. The whole operation takes about 2 seconds to generate 30 years of dates, which is a hundred times faster than the WHILE loop.

The only drawback is that since it uses temp tables, I don't think it can be included in a table function. This is not really a problem because both the sequential numbers and table of dates are VERY handy to have in any database, cost very little space, and perform much better than a dynamically generated list of dates (please trust me on this... I learned the hard way).

Here is the whole script...

/* Props to Jeff Moden for the sweet IDENTITY trick */

DECLARE @DaysFromGoLive int /*The date you want to start your dates on, in this case Jan 1, 2008*/
SET @DaysFromGoLive = (SELECT (DATEDIFF(dd,'01/01/2008',GETDATE()) + 1))

 SELECT TOP 10950 --30 years of days
        IDENTITY(INT,1,1) as N
   INTO #Numbers
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

CREATE TABLE [dbo].[TableOfDates](
	[fld_date] [datetime] NOT NULL,
 CONSTRAINT [PK_TableOfDates] PRIMARY KEY CLUSTERED 
(
	[fld_date] ASC
)WITH FILLFACTOR = 99 ON [PRIMARY]
) ON [PRIMARY]


INSERT INTO
      dbo.TableOfDates
SELECT 
      DATEADD(dd,nums.n - @DaysFromGoLive,CAST(FLOOR(CAST(GETDATE() as FLOAT)) as DateTime)) as FLD_Date
FROM #Numbers nums

SELECT MIN(FLD_Date) FROM dbo.TableOfDates
SELECT MAX(FLD_Date) FROM dbo.TableOfDates

DROP TABLE #Numbers

Also note the...

CAST(FLOOR(CAST(<your date=""> as FLOAT)) as DateTime)</your>

...this keeps the date time as numbers (chops off the time portion and converts it back to date), and performs better than the string comparisons (datepart = 'year'). When joining this table in, use this trick on the table you are joining to the TableOfDates, and add

AND MissingDataTable.DateColumn IS NULL

to the were clause so that the date is listed even if there is no data in the MissingDataTable.

Do you mean we should have a permanent table storing the date?
I do agree it's better to have a permanent one instead of generate it everytime.

I tried my while loop and it can generate the 30 year of day in 2 seconds too.

Yes, its generally a good ide to have the numbers and table of dates as permanent tables in at least one database on the server so that you can join them in for reports and other admin or maintenance functions.

While loops (and procedural logic) are not set based operations and will almost always perform worse than set ops in a relational database. They have thier place and are necessary sometimes, but in general you should try to avoid them when possible or move them to the calling application.

The speed of an operation is not the only factor to consider; cpu, disk IO, and memory should also be considered when doing basic performance benchmarking.

WPKF wrote:
I tried my while loop and it can generate the 30 year of day in 2 seconds too.

Agreed... but the estimate on how long the Tally table version would take was way too high (proof lies below). Further, a While loop doesn't allow for the very high speed TVF form of a user defined function. Take a look at the following equivalent code...

 CREATE FUNCTION dbo.jbmDateTable
     -- Created by Jeff Moden
        (
        @StartDate DATETIME,
        @EndDate   DATETIME,
        @DayPart   VARCHAR(5) -- support 'day','month','year','hour', default 'day'
        )
RETURNS TABLE
     AS
 RETURN
(
         WITH cteTally AS
        (
         SELECT TOP (
                     CASE @DayPart 
                     WHEN 'day'   THEN DATEDIFF(dd,@StartDate,@EndDate)
                     WHEN 'month' THEN DATEDIFF(mm,@StartDate,@EndDate)
                     WHEN 'year'  THEN DATEDIFF(yy,@StartDate,@EndDate)
                     WHEN 'hour'  THEN DATEDIFF(hh,@StartDate,@EndDate)
                     END  + 1
                     )
                ROW_NUMBER() OVER (ORDER BY GETDATE())-1 AS N
           FROM Master.sys.All_Columns sc1
          CROSS JOIN Master.sys.All_Columns sc2
        )
         SELECT CASE @DayPart 
                WHEN 'day'   THEN DATEADD(dd,t.N,@StartDate)
                WHEN 'month' THEN DATEADD(mm,t.N,@StartDate)
                WHEN 'year'  THEN DATEADD(yy,t.N,@StartDate)
                WHEN 'hour'  THEN DATEADD(hh,t.N,@StartDate)
                 END AS Date
           FROM cteTally t
)
;

Like your code, the code above provides the correct date info and doesn't use a permanent Tally table, but that's where the similarity ends. When I run the code below on my 7 year old 1.8GHz single CPU box...

 PRINT '========== Original Function =========='
   SET STATISTICS TIME ON
SELECT * FROM dbo.fnDateTable('20000101','20300101','day')
   SET STATISTICS TIME OFF

 PRINT '========== cteTally Function =========='
   SET STATISTICS TIME ON
SELECT * FROM dbo.jbmDateTable('20000101','20300101','day')
   SET STATISTICS TIME OFF

... here's what I get for stats...

========== Original Function ==========

(10959 row(s) affected)

SQL Server Execution Times:
   CPU time = 2140 ms,  elapsed time = 3575 ms.
========== cteTally Function ==========

(10959 row(s) affected)

SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 323 ms.

Please, folks... treat 99.99% of all While Loops like drugs... Just say "NO!" Wink | ;)

If you can't think of a way to do something without a While Loop, get on a good SQL Server forum and ask an expert how to do it with a Tally or Numbers table. Except for certain control loops (not RBAR), there's no reason to use While Loops.

p.s. Truth be told, except for demonstration purposes, I'd never write a function for this... direct code will be even faster because there will be no CASE statements.

That's a very large hammer you're using to solve a problem that could be solved using a different technique.

cheers,
Chris Maunder

CodeProject.com : C++ MVP

Someone said "ride the mammoth to catch the grasshopper"

I think convert datefield to char first you can easily group them

convert (char(10), datefield, 21 )

pop k.

yeah..thanks for your comment. Different problem uses different techniques to solve.
This article is no way to best and is "beginner level". Just one of the method to solve the problem in my current job. (Well, maybe it is not the best for my problem also.:P)

Date	Number of User
2008-05-01	3
2008-05-02	5
2008-05-04	6

Date	Number of User
2008-05-01	3
2008-05-02	5
2008-05-03	0
2008-05-04	6

Generate Date Table in SQL Server

Background

Points of Interest

License

Comments and Discussions