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)

1 comment:

Ritesh said...

Query to get all the dates within a date range:
I copied from this link:
http://www.c-sharpcorner.com/Blogs/692/


DECLARE @StartDate DATETIME,@EndDate DATETIME

SELECT @StartDate = '20080101',@EndDate = '20080930'

SELECT DATEADD(day, z.num, @StartDate)

FROM (

SELECT b10.i + b9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i num

FROM (SELECT 0 i UNION ALL SELECT 1) b0

CROSS JOIN (SELECT 0 i UNION ALL SELECT 2) b1

CROSS JOIN (SELECT 0 i UNION ALL SELECT 4) b2

CROSS JOIN (SELECT 0 i UNION ALL SELECT 8) b3

CROSS JOIN (SELECT 0 i UNION ALL SELECT 16) b4

CROSS JOIN (SELECT 0 i UNION ALL SELECT 32) b5

CROSS JOIN (SELECT 0 i UNION ALL SELECT 64) b6

CROSS JOIN (SELECT 0 i UNION ALL SELECT 128) b7

CROSS JOIN (SELECT 0 i UNION ALL SELECT 256) b8

CROSS JOIN (SELECT 0 i UNION ALL SELECT 512) b9

CROSS JOIN (SELECT 0 i UNION ALL SELECT 1024) b10

) z

WHERE z.num <= DATEDIFF(day, @StartDate, @EndDate)

ORDER BY z.num