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.
EDIT: Since folks seem to have questions about the tally table, let me rewrite this using a zero-based tally table. First, here’s some code to create and populate a table.
CREATE TABLE [dbo].[nbrs]( [nbr] [INT] NOT NULL ) ON [PRIMARY] GO CREATE UNIQUE CLUSTERED INDEX [clidx] ON [dbo].[nbrs] ( [nbr] ASC ) GO INSERT INTO dbo.nbrs (nbr) SELECT nbr-1 FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr FROM sys.columns c ) nbrs GO
Now, that you have the numbers table as a permanent object in your database, you can reuse it for the query INSTEAD of the subquery. The query has also been edited to use a zero-based calculation.
DECLARE @StartDate DATE = '20110901' , @EndDate DATE = '20111001' SELECT DATEADD(DAY, nbr, @DateStart) FROM nbrs WHERE nbr <= DATEDIFF(DAY, @DateStart, @DateEnd)
Performant, and no recursion.
Answered By – Stuart Ainsworth
Answer Checked By – Robin (AngularFixing Admin)