Wednesday, February 1, 2012

Use of SQL temp table to show 24hours as data set

Query:
declare @t as int
select @t = 0
declare @developers table(t varchar(12))
while (@t < 24)
begin
if (@t < 10)
begin
insert into @developers (t) values ('0' + CONVERT(varchar, @t) + ':00:00:000')
end
if (@t >= 10)
begin
insert into @developers (t) values (CONVERT(varchar, @t) + ':00:00:000')
end
select @t = @t +1;
end
select @t = @t -1;
insert into @developers (t) values (CONVERT(varchar, @t) + ':59:59:999')
select t as 'TimeValue' from @developers

Output:

TimeValue
------------
00:00:00:000
01:00:00:000
02:00:00:000
03:00:00:000
04:00:00:000
05:00:00:000
06:00:00:000
07:00:00:000
08:00:00:000
09:00:00:000
10:00:00:000
11:00:00:000
12:00:00:000
13:00:00:000
14:00:00:000
15:00:00:000
16:00:00:000
17:00:00:000
18:00:00:000
19:00:00:000
20:00:00:000
21:00:00:000
22:00:00:000
23:00:00:000
23:59:59:999
(25 row(s) affected)