Click here to Skip to main content
Click here to Skip to main content

Tagged as

Generate Date Table in SQL Server

, 1 Jun 2008
Rate this:
Please Sign up or sign in to vote.
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)

Share

About the Author

WPKF
Web Developer
Hong Kong Hong Kong
A .NET Programmer for 8 years, who wants to share the experience.

Comments and Discussions

 
GeneralA more robust approach based on SQL Guru Jeff Moden's numbers table Pinmemberastanton19783-Jun-08 2:43 
GeneralRe: A more robust approach based on SQL Guru Jeff Moden's numbers table PinmemberWPKF3-Jun-08 16:30 
GeneralRe: A more robust approach based on SQL Guru Jeff Moden's numbers table Pinmemberastanton19784-Jun-08 5:32 
GeneralRe: A more robust approach based on SQL Guru Jeff Moden's numbers table PinmemberJeff Moden13-Sep-09 15:50 
GeneralVery large hammer PinadminChris Maunder2-Jun-08 2:12 
GeneralRe: Very large hammer PinmemberMember 14586602-Jun-08 3:13 
GeneralRe: Very large hammer PinmemberWPKF2-Jun-08 15:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140814.1 | Last Updated 2 Jun 2008
Article Copyright 2008 by WPKF
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid