Sql – How to solve Conversion failed when converting the nvarchar value

sqlsql serversql-server-2005

CREATE TABLE #Temp (VisitingCount int, [Time] int, [Date] nvarchar(50) )
DECLARE @DateNow DATETIME,@i int,@Time int, @Date nvarchar(50)
set @DateNow='00:00'  
set @i=1;  
while(@i^60;48)  
    begin  
        set @DateNow = DATEADD(minute, 30, @DateNow)
        set @Time = (datepart(hour,@DateNow)*60+datepart(minute,@DateNow))/30 
        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],Date
from (
  select Sum(VisitingCount)as VisitingCount, [Time],Date
    from #Temp group by [Time],Date
  Union All
    select count(page) as VisitingCount,CONVERT(VARCHAR(5),Date, 108), 
    (datepart(hour,Date)*60+datepart(minute,Date))/30 as [Time]
    from scr_SecuristLog
    where Date between '2009-05-12' and '2009-05-13'
    GROUP BY (datepart(hour,Date)*60+datepart(minute,Date))/30,CONVERT(VARCHAR(5),Date, 108)
  ) X
group by [Time],Date
order by 2 asc  

Error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '00:30' to data type int.

ERROR AREA: select Sum(VisitingCount)as VisitingCount, [Time],Date……………..

Best Answer

In the second part of your union the data types used are (int, date, time) whereas in the first part they are (int, time, date). Try changing the order of the columns in either the first or the second part of the union.

Related Topic