How to change nvarchar to datetime format?

Question

My code generates some dataset per 10 minutes-log or per 30 minutes-log, but the DateTime Format is '1900-01-01 14:20:00'. I need '2009-05-13 14:20:00', so I want to change it.

See Table1 and Table2 below.

declare @date1 nvarchar(100) , @date2 nvarchar(100) , @countgap int,@count int 
set @date1='2009-05-12'
set @date2 = '2009-05-13'
set @countgap = 10 --per ten minutes


CREATE TABLE #Temp (VisitingCount int, [Time] int, [Date] datetime )
DECLARE @DateNow DATETIME,@i int,@Time int, @Date datetime
set @DateNow='00:00'  
set @i=1;  
insert into #Temp(VisitingCount,[Time],[Date]) values(0,0,@DateNow )
while(@i<(24*60)/@countgap)  
    begin  
        set @DateNow = DATEADD(minute, @countgap, @DateNow)
        set @Time = (datepart(hour,@DateNow)*60+datepart(minute,@DateNow))/@countgap 
     set @Date = CONVERT(VARCHAR(5),@DateNow, 108)
        insert into #Temp(VisitingCount,[Time],[Date]) values(0,@Time,@Date )
        set @i=@i+1
    end

select 
Sum(VisitingCount) as VisitingCount, [Time], 
Cast( RIGHT('00'+Cast([Time]*@countgap/60 as varchar(2)  ),2) +':'+RIGHT('00'+Cast( [Time]*@countgap%60 as varchar(2)  ),2)  as Smalldatetime)as VisitingGap
from (
  select 0 as VisitingCount, [Time] from #Temp
  Union All
    select count(page) as VisitingCount, 
    (datepart(hour,Date)*60+datepart(minute,Date))/@countgap as [Time] 
    from scr_SecuristLog
    where Date between @date1 and @date2
    GROUP BY (datepart(hour,Date)*60+datepart(minute,Date))/@countgap
  ) X
group by [Time]
order by  2 asc

Table1

VCount  Time    VisitingGap
 6      86      1900-01-01 14:20:00
17      87      1900-01-01 14:30:00
 9      88      1900-01-01 14:40:00
20      89      1900-01-01 14:50:00
19      90      1900-01-01 15:00:00
 0      91      1900-01-01 15:10:00
 7      92      1900-01-01 15:20:00
 4      93      1900-01-01 15:30:00
 8      94      1900-01-01 15:40:00
 9      95      1900-01-01 15:50:00
 0      96      1900-01-01 16:00:00
20      97      1900-01-01 16:10:00
24      98      1900-01-01 16:20:00

Table2

VCount  Time    VisitingGap
...
 6      86      2009-05-13 14:20:00
17      87      2009-05-13 14:30:00
 9      88      2009-05-13 14:40:00
20      89      2009-05-13 14:50:00
19      90      2009-05-13 15:00:00
...

I guess I need to change this:

Cast( RIGHT('00'+Cast([Time]*@countgap/60 as varchar(2)  ),2) +':'+RIGHT('00'+Cast( [Time]*@countgap%60 as varchar(2)  ),2)  as Smalldatetime)as VisitingGap

Answer 1

Why don't you declare your date variables as datetime?

  declare @date1 datetime
  declare @date2 datetime

  set dateformat ymd

  set @date1 = '2009-05-12'
  set @date1 = '2009-05-13'

  ...

This way, you don't have to do any conversions from nvarchar to datetime.

Or, if data comes from outside, using set dateformat, you could convert them in first place.

Answer 2

You are using a SmallDateTime value. This has a minimum value of 01/01/1900.

I see that you set your DateNow to '00:00' This will set it to the date minimum value plus the time '00:00'.

You need to set it to the current date or at least whatever start date your timer is based on first.