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

Question

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 thsi 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 1

@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

Answer 2

SQL Server 2008 includes the concept of a time data type.

Check this out:

CREATE TABLE dbo.Table_1 ( testtime time(7) NULL, testdate date NULL, testdatetime datetime NULL ) ON [PRIMARY] GO

insert table_1 (testtime, testdate, testdatetime) values (GETDATE(), GETDATE(), GETDATE())

CREATE TABLE dbo.Table_2 ( testtime time(0) NULL, testdate date NULL, testdatetime datetime NULL ) ON [PRIMARY] GO insert table_2 (testtime, testdate, testdatetime) values (GETDATE(), GETDATE(), GETDATE())

select * from Table_2 select * from Table_1