@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
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
