Click here to Skip to main content
15,742,973 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a SQL Query with multiple columns including of type timestamp without time. I wanted to output the time as well as the count(*) and group the data by hour and by every 15 minutes. I wrote the query as below as general.

But, it is not working.

Query :
SELECT CAST(Ptime as Date) AS dt,
       extract(hour from Ptime) as hr,
       floor(extract(minute from Ptime)/15) * 15 AS mn,
       count(*) as thecount
FROM log WHERE CAST(Ptime As Date) >= '09/23/2010'
GROUP BY CAST(Ptime as Date),
         extract(hour from Ptime),
         floor(extract(minute from Ptime)/15) * 15
ORDER BY dt, hr, mn
Prasad_Kulkarni 28-May-12 8:23am    
What's the error gani??
db7uk 28-May-12 8:39am    
Extract is not (I believe) an SQL server function. It is a MySQL function. It maybe that the error is being thrown for that reason or that it does not support aggregation.
gani7787 28-May-12 9:13am    
How we can write query based on my condition in sql server...?

1 solution

using datepart function you can solve your problem

like :-
SELECT CAST(Ptime as DateTime) AS dt,datepart(month,CAST(Ptime as DateTime)),datepart(hour,CAST(Ptime as DateTime)),datepart(minute,CAST(Ptime as DateTime))

SQL 2005 DateTime Functions[^]
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900