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