Set Operation trick to generate date data #TSQL2sDay

By Rajib Bahar at December 08, 2009 17:52
Filed Under: SQL

Two things that happened today, which motivated me to write this post: 

1. I learned a new trick using set-operation

2. Also today is the day some of my SQL Server colleagues having #TSQL2sDay party

 

Here is the entry where I learned the trick [http://ask.sqlteam.com/questions/1206/insert-date-value-for-1-year]. 

 

I have been participating at AskSqlTeam.com lately. One of the recent question was about how to generate date for 1 year. TG had an interesting solution and I did not realize it was set-operation until Kristen set me straight. :) As usual, I was coming up with a iterative solution as opposed to set-based one. Here is the snippet I modified off of TG's code.

create table #myTable 
(dateCol datetime)
go
declare @i int
SET @i = 0
WHILE 
(
 datediff(year, dateadd(day, @i,'2010-01-01'),'2010-01-01')=0
)
begin
    insert #myTable (dateCol)
    select dateadd(day, @i, '2010-01-01')
    SET @i = @i + 1
    continue
end
go
select * from #myTable
go


The script above will create about 365 entries containing everyday of the year 2010. 

TG's answer to that problem was:

create table #myTable 
(dateCol datetime)
go
insert #myTable (dateCol)
select dateadd(day, number, '2010-01-01')
from   master..spt_values
where  type = 'P'and    number < 365
order by number
go
select * from #myTable
go

 

Anyways, my approach above is not the most ideal solution and it will be slow because of the looping. TG had the right idea. Many DB professional create a reference database for their tasks. In this database one may have scripts that can be applied on scheduled jobs and other artifacts that don't belong anywhere else. The solution above can be improved if we create a reference table containing all integers. Let's say we call that table IntValues. I started building that table today and it took more than 6 hours to enter 2 billion numbers. Here is the final draft of what that script would look like.

 

create table #myTable 
(dateCol datetime)
go
insert #myTable (dateCol)
select dateadd(day, number, '2010-01-01')
from   DbReference.dbo.IntValues
where  number < 365
order by number
go
select * from #myTable
go

Tag cloud

Month List