I need to populate a table that will store the date ranges between 2 given dates: 09/01/11 - 10/10/11
So in this case the table would start from 09/01/11 and store each day till it got to 10/10/11
I was wondering if there was a slick way of doing this in SQL Server - I am currently using SQL Server 2008. Thanks
Easy on SQL 2005+; easier if you have a numbers or tally table. I faked it below:
DECLARE @StartDate DATE = '20110901' , @EndDate DATE = '20111001' SELECT DATEADD(DAY, nbr - 1, @StartDate) FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr FROM sys.columns c ) nbrs WHERE nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)
If you have a tally table, replace the subquery with the table. No recursion.