Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
kiranv5
Starting Member
1 Post |
Posted - 2014-03-18 : 13:02:06
|
I am trying to get hourly data on a table which was working fine on mysql but not on sql. how to use this in sql server managmenmt studio, i receive 'time' is not a recognized function name.and curdate() is not recognise function.select [pa_number], [pa_surname] ,[pa_forename], sum(time(datetime) >= '07:00:00' and time(datetime) < '08:00:00') as '7.00-8.00 AM', sum(time(datetime) >= '08:00:00' and time(datetime) < '09:00:00') as '8.00-9.00 AM', sum(time(datetime) >= '09:00:00' and time(datetime) < '10:00:00') as '9.00-10.00 AM ', sum(time(datetime) >= '10:00:00' and time(datetime) < '11:00:00') as '10.00-11.00 AM', sum(time(datetime) >= '11:00:00' and time(datetime) < '12:00:00') as '11.00-12.00 AM', sum(time(datetime) >= '12:00:00' and time(datetime) < '13:00:00') as '12.00-1.00 PM', sum(time(datetime) >= '13:00:00' and time(datetime) < '14:00:00') as '1.00-2.00 PM', sum(time(datetime) >= '14:00:00' and time(datetime) < '15:00:00') as '2.00-3.00 PM', sum(time(datetime) >= '15:00:00' and time(datetime) < '16:00:00') as '3.00-4.00 PM', sum(time(datetime) >= '16:00:00' and time(datetime) < '17:00:00') as '4.00-5.00 PM', sum(time(datetime) >= '17:00:00' and time(datetime) < '18:00:00') as '5.00-6.00 PM', sum(time(datetime) >= '18:00:00' and time(datetime) < '19:00:00') as '6.00-7.00 PM' from [ICPS].[dbo].[parking_attendants] t inner join tickets a on t.[pa_number] = a.[t_pa_number] where cast(t.DATETIME AS DATE) = CURDATE() group by pa_surname |
|
maunishq
Yak Posting Veteran
71 Posts |
Posted - 2014-03-19 : 10:51:50
|
In sql server we use GETDATE() in place of CURDATE()Why are you using time function? Is [datetime] name of your column?=======================Not an Expert, Just a learner.!_(M)_! |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2014-03-21 : 05:22:43
|
Also instead of time(datetime) use datepart(hour,datetime)MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|