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.

What database product are you using?

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

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).


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

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).

It worked.
Beautiful!

Thank you.

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

Nice!
Bookmarked.
Thanks.