Generate Date Table in SQL Server
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.