Generating an Time table in SQL
In reporting queries it happens often that you need a ruler of some sort for instance a table containing all days between a start and end date. You can generate such tables by using a looping construct but this isn't the strong point of SQL.
Using a single table containing all numbers between 0 and 59 this query below can generate a time table with each year,month,day,hour and minute between 1980 and 2039 (this range can be moved or changed).
select 1980 + y.value, m.value, d.value , h.value, mm.value
from [time] y, [time] m, [time] d, [time] h, [time] mm
where m.value between 1 and 12
and (d.value between 1 and case when m.value in (1,3,5,7,8,10,12) then 31 when m.value = 2 then case when y.value % 400 = 0 then 29 when y.value % 100 = 0 then 28 when y.value % 4 = 0 then 29 else 28 end else 30 end)
and h.value between 0 and 23
order by y.value, m.value, d.value, h.value, mm.value
To generate the table you can use the statement below
create table [time] ( [value] int )
declare @i int
set @i = 0
while @i < 60 begin
insert into [time] values (@i)
set @i = @i +1
end
Wednesday, March 05, 2008