Many times when developing SQL Server databases you have the requirement to show all dates in a range. One way to do this is with a date table. One of the most common reasons that I use a date table is when I have a reconciliation report. In a reconciliation report I want to show all of the days in a date range, even if they don’t have data on those days. SQL Server doesn’t have a function to generate dates in a range. So to accomplish this, I create a date table using the following script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE DateTable (DateValue DATETIME) GO DECLARE @StartDate DATETIME = '01/01/2014' DECLARE @NumberOfDays INT = 3000 INSERT INTO DateTable (DateValue) SELECT DATEADD(DAY, RowNum, @StartDate) AS DateValue FROM (SELECT TOP (@NumberOfDays) p1.PARAMETER_ID, ROW_NUMBER() OVER (ORDER BY p1.PARAMETER_ID) - 1 AS RowNum FROM sys.all_parameters p1 CROSS JOIN sys.all_parameters p2) NumberTable |
Wow the worst example possible…..what if the dates are British?!>