SQL Query Timestamp group by date

I have a query in which I want to sort the results by date.
I only have a timestamp field (named: opened), with which to use.

An entry of the timestamp field looks like this:
5/11/2008 12:00:26 AM

Here is my query:

SELECT t.opened, (select count(environment) from Current where opened=t.opened) AS TOTAL, (select count(environment) from Current where environment="P" and opened=t.opened) AS PROD, (select count(environment) from Current where environment="C" and opened=t.opened) AS COB, (select count(environment) from Current where environment="U" and opened=t.opened) AS UAT, (select count(environment) from Current where environment="D" and opened=t.opened) AS DEV
FROM [Current] AS t
GROUP BY t.opened;

Unfortunately, it groups by the whole timestamp field, not by the date.
I want it to group by the date.

Please help.

 

Log in or Sign up to hide all adverts.

What database product are you using?

 

Log in or Sign up to hide all adverts.

Right now it is in MS Access, but I will likely convert it to SQL.

 

Log in or Sign up to hide all adverts.

So add date..

GROUP BY t.opened, t(?).Date(?)

Oh...you don't have date..

I guess you need another select(~parse the date alone in the field imtoolazytodoit)

 

I don't know much about Access, it does seem to have a function DateValue that extracts the Date portion from the timestamp. You would replace every reference to opened with DateValue(opened).

As a side note I would write the SQL you gave in the following way (but since I don't know Access I am not sure if it would be valid).

Code:

select DateValue(opened) as OPENED, 
       count(environment) as TOTAL, 
       sum(case environment when 'P' then 1 else 0 end) as PROD, 
       sum(case environment when 'C' then 1 else 0 end) as COB,
       sum(case environment when 'U' then 1 else 0 end) as UAT,
       sum(case environment when 'D' then 1 else 0 end) as DEV
from [Current]
group by DateValue(opened)

 

Thanks.

I'll take a stab at it when I get back to work.

 

Dude,

Check this link out:
http://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm

 

It worked.
Beautiful!

Thank you.

Nice!
Bookmarked.
Thanks.