Overview
The following script allows you to get one row for every date in a range with extra information attached to that date.This is an alternative way of building a date table in sql. You could also use this in a view with the date range hard-coded
It is also a very efficient way of expanding out the dates as the CTEs are like macros rather than temp tables so it doesn't actually create millions of rows for every date possibility.
The original script I found on http://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function which I have added to.
Script
DROP FUNCTION [dbo].[udfExplodeDates]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jeremy
-- Create date: Sep 2012
-- Description: Explodes out each day in a date range to have one row per day, with some extra info
-- Use CTEs to join onto itself to create millions of rows and then uses the row_number
-- to figure out how many days to go from the start date
-- =============================================
CREATE FUNCTION [dbo].[udfExplodeDates](@startdate datetime, @enddate datetime)
returns table as
return (
with
R0 as (SELECT 1 as n UNION ALL SELECT 1) -- 2 rows
,R1 as (SELECT 1 as n FROM R0 t1, R0 t2) -- 4 rows
,R2 as (SELECT 1 as n FROM R1 t1, R1 t2) -- 16 rows
,R3 as (SELECT 1 as n FROM R2 t1, R2 t2) -- 256 rows
,R4 as (SELECT 1 as n FROM R3 t1, R3 t2) -- 65536 rows
,R5 as (SELECT 1 as n FROM R4 t1, R4 t2) -- 65536*65536 rows = 4,294,967,296 rows
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM R5)
SELECT DATEADD(day,num-1,@startdate) as the_date,
DATENAME(dw,DATEADD(day,num-1,@startdate)) as day_of_week,
day(DATEADD(day,num-1,@startdate)) as day_of_month,
EOMONTH(DATEADD(day,num-1,@startdate)) as end_day_of_month --only available in SQL2012 onward
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);
GO
Example Output
