February 6, 2013 at 7:43 am
Good Afternoon
I am trying to create 1 column on a table with a date that starts 01/01/2010, which then automatically increases until 31/03/2015.
I know this is really basic but I am stuck, below is the code I have written already, what have I missed and what have I done wrong:
USE Occupancy
CREATE TABLE Time2
(Datedate not null)
DECLARE @Date date
SET @Date = '01/01/2010'
INSERT INTO Time2
DO WHILE @Date
VARIABLE @date Dateadd +1
Any help would be most welcome.
Thanks
Wayne
February 6, 2013 at 8:18 am
You may be looking for something more like this which creates 10 years of dates from the BaseDate.
There are two versions one using a While loop and one with a hacked together Tally Table.
CREATE TABLE #Time2
(Date date not null)
CREATE TABLE #Time3
(Date date not null)
DECLARE @BaseDate DateTime
,@NumberOfDays int
,@counter int
SET @BaseDate = '01/01/2010'
SET @NumberOfDays = 3650
SET @Counter=0
Declare @St DateTime=GetDATE()
WHILE @Counter<@NumberOfDays
BEGIN
INSERT INTO #Time2
Select convert(Date,DateAdd(d,@counter,@BaseDate)) Date
Set @Counter=@Counter+1
END
Print 'Time Taken While : '+Convert(varchar(100),datediff(ms,@St,GetDATE()))
SET @St=GetDATE()
;With CTE as (
Select Row_NUMBER() OVER (ORDER BY a.object_id) a
From sys.objects a,sys.objects b
)
Insert into #Time3
Select convert(DAte,DateAdd(d,a-1,@BaseDate))
From CTE
Where a between 1 and @NumberOfDays
Print 'Time Taken CTE : '+Convert(varchar(100),datediff(ms,@St,GetDATE()))
Hopefully this highlights why loops are generally bad in SQL, on my system
the While takes 200-250 ms (regardless)
the Set based query takes just 30-35ms (45-50ms if run as the first query)
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 6, 2013 at 8:27 am
Please do not cross post. This is basically asking the same thing as here: http://www.sqlservercentral.com/Forums/Topic1416459-1292-1.aspx#bm1416511.
Please post further replies at the above link.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply