Getting error when using Hour,minute using extract function

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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

SQL

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

Posted 28-May-12 2:22am

gani7787

Add a Solution

Comments

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

Add a Solution

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

Prosan · Accepted Answer · 2012-05-28T03:16:00

Solution 1

using datepart function you can solve your problem

like :-

SQL

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[^]

Posted 28-May-12 3:16am

Prosan

Getting error when using Hour,minute using extract function

1 solution

Solution 1

Add your solution here

Preview 0

Getting error when using Hour,minute using extract function

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us