Penguen Penguen - 22 days ago 8
SQL Question

How can i create this time-table with stable time format?

How can i get result "My Dream Table Result"

CREATE TABLE #temp(
[count] int
, [Time] nvarchar(50))
DECLARE @DateNow DATETIME,@i int

SET @DateNow='00:00'
SET @i=1;
WHILE(@i<1440)
BEGIN
SET @DateNow=DATEADD(minute, 1, @DateNow)
INSERT INTO #temp ([count], [Time]) VALUES (0, @DateNow)
SET @i=@i+1
END
SELECT [count],CONVERT(VARCHAR(max), [Time], 104) AS [Time] FROM #temp
DROP TABLE #temp


Table Result:

Count | Time
-----------------------------
0 | Jan 1 1900 12:01AM
0 | Jan 1 1900 12:02AM
0 | Jan 1 1900 12:03AM
0 | Jan 1 1900 12:04AM


But i don't like this table Time format is not ok. I need this table
My Dream Table Result:

Count | Time
0 | 12:01
0 | 12:02
0 | 12:03
0 | 12:04
...
...
0 | 22:01
0 | 22:02
0 | 22:03
0 | 22:04

Answer Source

@DateNow is a DateTime so will have the Date Compoent as well

select right(left(convert(nvarchar, @DateNow, 121), 16), 5)

will give you just the time component for putting in yoru Nvarchar column

while(@i<1440)
begin
    set @DateNow=DATEADD(minute, 1, @DateNow)
    insert into #temp ([count], [Time]) values(0, right(left(convert(nvarchar, @DateNow, 121), 16), 5))
    set @i=@i+1
end
SELECT [count], [Time] from  #temp
drop table #temp

And for shits'n'giggles, here's how to do it without relying on a DateTime Object.

CREATE TABLE #temp (
   [count] int,
   [Time] nvarchar(50) 
)


DECLARE @hour int, @min int, @i int
SELECT @hour = 0, @min = 0, @i = 1

WHILE (@hour < 24)
BEGIN
    WHILE (@min < 60)
    BEGIN
        INSERT INTO #temp ([count], [time]) 
        VALUES (@i, REPLACE(STR(@hour, 2, 0), ' ', 0) + ':' + REPLACE(STR(@min, 2, 0), ' ', 0))
        set @min = @min + 1
        set @i=@i+1
    END     
    set @min = 0
    set @hour = @hour + 1
END

SELECT * FROM #Temp