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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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