Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

Generate Date Table in SQL Server

2.44/5 (6 votes)
1 Jun 2008CPOL 1  
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


DateNumber of User
2008-05-013
2008-05-025
2008-05-046

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

DateNumber of User
2008-05-013
2008-05-02
5
2008-05-030
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)