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.
DECLARE @StartDate DATETIME = '01/01/2014'
DECLARE @NumberOfDays INT = 3000
SELECT DATEADD(DAY, RowNum, @StartDate) AS DateValue
FROM (SELECT TOP (@NumberOfDays)
ROW_NUMBER() OVER (ORDER BY p1.PARAMETER_ID) - 1 AS RowNum
FROM sys.all_parameters p1
CROSS JOIN sys.all_parameters p2) NumberTable
1 thought on “How To Create A Date Table”
Wow the worst example possible…..what if the dates are British?!>