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)
Subscribe to:
Post Comments (Atom)
1 comment:
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
Post a Comment