Create (n) number of records based on date range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Create (n) number of records based on date range

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-11 : 16:59:02

Ok, I have two parameters - @StartDate and @EndDate. We only care about the date part of these paramters. What I would like to do is create a table with one record for each date between these two values. For example:

@StartDate = '01/01/2008'
@EndDate = '01/8/2008'

Should yield a table with 9 records in it for every day between @StartDate and @EndDate like so:

01/01/2008 <datacol1> <datacol2>
01/02/2008 <datacol1> <datacol2>
01/03/2008 <datacol1> <datacol2>
01/04/2008 <datacol1> <datacol2>
01/05/2008 <datacol1> <datacol2>
01/06/2008 <datacol1> <datacol2>
01/07/2008 <datacol1> <datacol2>
01/08/2008 <datacol1> <datacol2>

I know I could just do a WHILE (@StartDate <= @EndDate) loop and insert records into a temp table but I'm looking to see if there are any new methods/techniques to achieve this with a more simple statement.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-11 : 17:22:35

Just an FYI - after I posted here I did some additional searching and ran across a method that seems pretty slick:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()-8), 0);
SET @EndDate = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0);

WITH Calendar (Date)
AS
(
SELECT @StartDate
UNION ALL
SELECT DATEADD(DAY, 1, Date)
FROM Calendar
WHERE DATEDIFF(DAY, Date, @EndDate) > 0
)
SELECT *
FROM Calendar
OPTION (MAXRECURSION 0)

I found it from this link - http://blogs.conchango.com/jamiethomson/archive/2007/01/11/T_2D00_SQL_3A00_-Generate-a-list-of-dates.aspx.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-11 : 18:56:23

If you need a more full featured date table, you may find this useful:
Date Table Function F_TABLE_DATE
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

CODO ERGO SUM

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-12 : 08:54:58

I think this is a little bit much for what I need. I just need to prepopulate a result set with every date between two dates, inclusive of those two dates. However, this is a function I might steal parts of for other needs as they arise :).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources