Generate Date Table in SQL Server

WPKF

Rate me:

2.44/5 (6 votes)

1 Jun 2008CPOL

50.7K

Generate Date Table in SQL Server

Background

In many reports, one of the axis will be the a date range, either monthly or daily. However, there may be no data in a specific month or on a specific day. There will be no record when joining different tables. For example, if no user on 2008-05-03, the row of 2008-05-03 will not appear

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

This SQL script generates a date range for left/right joining other tables so the result will include date with no data.

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

Create Function dbo.fnDateTable
(
  @StartDate datetime,
  @EndDate datetime,
  @DayPart char(5) -- support 'day','month','year','hour', default 'day'
)
Returns @Result Table
(
  [Date] datetime
)
As
Begin
  Declare @CurrentDate datetime
  Set @CurrentDate=@StartDate
  While @CurrentDate<=@EndDate
  Begin
    Insert Into @Result Values (@CurrentDate)
    Select @CurrentDate=
    Case
    When @DayPart='year' Then DateAdd(yy,1,@CurrentDate)
    When @DayPart='month' Then DateAdd(mm,1,@CurrentDate)
    When @DayPart='hour' Then DateAdd(hh,1,@CurrentDate)
    Else
      DateAdd(dd,1,@CurrentDate)
    End
  End
  Return
End

Points of Interest

Parameterized User-defined function is very useful. And caller to the function can use the result directly just like a table.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

WPKF

Web Developer

Hong Kong

A .NET Programmer for 8 years, who wants to share the experience.

Comments and Discussions

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.

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.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.