Autogenerate dates
-
I'm sure someone has done this before, but I can't seem to find a thread regarding it.
I've got a "time" table in my Datawarehouse that I need to generate daily dates for. I haven't been given an "enddate", so I figure I'll go out 5 or 10 years just to be safe. I need a date for every single calendar date from now until whenever and I need to associate it with an identity field.
So here's what I'm thinking. Create Table tblTime (TimeID int identity(1,1), EntryDate datetime)
Then do a While statement that counts down for the next X years and does an incremental DateAdd. Something like:
Declare @MyCount int, @CurDate datetime
Set @MyCount = 3560
Set @CurDate = '01/27/2008'
While @MyCount > 0
Begin
Insert into tblTime (EntryDate)
(Select @CurDate)
Set @MyCount = @MyCount - 1
Set @CurDate = DateAdd(dd,1,@CurDate)
End
Hmmm. That sounds more simple than it was in my head. @=)
Anyway, does anyone know of a better way to do it or am I pretty much on the money with this?
Thoughts, comments, and alternatives are always welcome. Thanks!
-
I've written a bunch of variations on this. Recursive CTE's work nice for this:
Recursion limits mean you have to hav a window that is within reason.
WITH DateList (DateValue, DateID)
AS (
SELECT GETDATE(), 0
UNION ALL
SELECT DateValue + 1, DateID + 1
FROM DateList
WHERE DateValue <= '12/31/2020'
)
SELECT
*
FROM
DateList
WHERE
DateValue < '1/1/2010'
OPTION
(MAXRECURSION 30000)
-
As long as you have a method, it doesn't matter if there's a better one. You're going to do this only once really and any method will only vary by a couple secs probably. So your solution looks just fine.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
First thought - you're doing this one time on 3000 or so rows, so it doesn't much matter HOW you do it.
Second thought - now that we've banished thought #1 from our minds and look for the efficient way to do this...
Recursion in my mind looks to be really overkill...
A tally table (or using the SQL 2005 ROW_NUMBER()) solution tends to run quite a bit faster:
use testing
--create table dates(id int identity(1,1) primary key clustered,dateval datetime)
declare @g datetime
declare @startdate datetime
declare @enddate datetime
declare @datescount int
select @startdate='01/01/2008',@enddate='12/31/2199'
select @datescount=datediff(day,@startdate,@enddate)
--tally/numbers table/ROW_NUMBER() approach
select @g=getdate() --just to measure
insert dates(dateval)
select top(@datescount)
dateadd(day,row_number() over (order by sc1.object_id),@startdate)
from sys.all_columns sc1, sys.all_columns sc2
select datediff(ms,@g,getdate())
truncate table dates
--recursive approach
select @g=getdate();
WITH DateList (DateValue, DateID)
AS (
SELECT @startdate, 0
UNION ALL
SELECT DateValue + 1, DateID + 1
FROM DateList
WHERE DateValue <= @enddate
)
insert dates(dateval)
SELECT
Datevalue
FROM
DateList
WHERE
DateValue < @enddate
OPTION
(MAXRECURSION 0)
select datediff(ms,@g,getdate())
About 300% faster than the recursive approach, and about 3000% faster than the loop. A permanent tally table would be just a bit faster than even the ROW_NUMBER() assuming the table's built, but for a "no traces left behind", that seems to work quite well.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Wow. These are all very interesting suggestions. I'm glad I posted this topic. @=)
I've had difficulty figuring out how to use CTEs with my normal code needs and this might help me out on two fronts: learning CTEs and autogenerating dates.
Thanks, everyone! I really appreciate your insights.
-
I agree with Matt... recursion is probably not the best way to do things like this...
There are variations on this theme but here's a nice little NON-recursive CTE that returns a 100 years (can be changed, of course) in the blink of an eye... it does 10 years in the proverbial "0 ms". This uses the same "ROW_NUMBER" solution that MATT pointed out.
SET STATISTICS TIME ON--===== Declare some local variables that could be parameters in a proc
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
--===== Set those "parameters" for demonstration purposes
SET @StartDate = '20000101' --Inclusive
SET @EndDate = '21000101' --Non-inclusive
; WITH cTally AS
(-----------------------------------------------------------------------------
--==== CTE equivalent of a Tally table
SELECT TOP (DATEDIFF(dd,@StartDate,@EndDate))
ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROM Master.sys.Columns t1
CROSS JOIN Master.sys.Columns t2
)-----------------------------------------------------------------------------
--===== Create the date range
SELECT N,
Date = DATEADD(dd,t.N-1,@StartDate),
NextDate = DATEADD(dd,t.N ,@StartDate)
FROM cTally t
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
That is a cute one there Jeff.
By the way - any one notice the little poison pill they snuck into the Deprecated features about the %$##%%^&* semi-colons? They're going to be REQUIRED after EACH T-SQL statement (yes - it's in some undisclosed future version, but still)....
I mean - I like C# and all, but what's with all the ^&&%%$##%^& punctuation???
I sure hope they provide some automated to put those all in....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
I've actually already started adding semi-colons because there are times where it seems to help my code perform better (I guess because SQL doesn't have to decide if one of my statements is a sub-statement to something else).
I haven't tested this, of course, to see what the execution plan is compared to the use of semi-colons verses not using them. But I do occasionally see a minor difference.
However, you would think Microsoft would announce code changes that they're going to *require* in other places that depreciated features. I mean, come on. Do they not know how much code everyone is going to have to change??? Or did it never occur to them that this one little thing is going to cause major database blowups?
-
Is this on the marked-for-deprecation list for 2K8 (meaning it won't happen until 2K11) ?
-
matt stockham (1/29/2008)
Is this on the marked-for-deprecation list for 2K8 (meaning it won't happen until 2K11) ?It's even more confusing than that. They now seem to have the "deprecated" list broken into two sections: one that is the stuff that is gone in the next version for sure, and one that represents things that will be removed *in some future version*. It seems that certain things keep graduating from the second list onto the first list as they are confirmed to be removed.
For now: the semi-colon thing, three and four- part names inside the SELECT portion of a query, and the ANSI_NULLS and ANSI_PADDING deprecation are all still on the "in some undetermined future version" section of the 2008 deprecated list. The non-ANSI joins, using ROWCOUNT on anything other than a SELECT, and sp_dboption are on the chopping block in the "next version" list.
It's a rather massive list, so it's worth reading through to see what might hit you squarely between the eyes....
http://msdn2.microsoft.com/en-us/library/ms143729(SQL.100).aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply