Grouping a date field by day

[Larshg]

Hi

I have this SQL
SELECT Count(Alarmer_Jan04.ID) AS CountOfID, Alarmer_Jan04.[Create Date]
FROM Alarmer_Jan04
WHERE (((Alarmer_Jan04.[MC-AlarmDescr]) Like '*Heartbeat*'))
GROUP BY Alarmer_Jan04.[Create Date];


But I want it to group it into days according to the [Create Date]

So that my output would be something like this
date number
2004-01-20 20


Thanks

 

[JonFer]

Don't understand what you mean by "group it into days according to the CreateDate" - can you post a few more examples?

If you want to group by the day of the create date (e.g. use 20 for 1/20/04 and 15 for 1/15/04) then what about when you have 1/20/04 and 2/20/04?

Or do you want to group by the number of days between the CreateDate and some other date?

 

[Larshg]

Hi

I want to group by the day of the create date.
so that it will give me the number af records pr day

so that a table that looks like this
2004-01-20 11:00
2004-01-20 12:00
2004-01-20 13:00
2004-01-25 12:00
2004-01-25 13:00
2004-01-27 00:00

gives me this output
2004-01-20 3
2004-01-25 2
2004-01-27 1

 

[JonFer]

Now that is clear enough. Use the Format(YourDate,"yyyy-mm-dd&quot function on your date and group by it.

 

[Larshg]

Cuul it works

But now I want to display all enteryes from one day

I trying to do some like this
SELECT Alarmer_Jan04.[MC-AlarmDescr], Alarmer_Jan04.AlarmTrapTime, Alarmer_Jan04.AckTIME, Alarmer_Jan04.ResolvedTIME, Format(Alarmer_Jan04.[Create Date],"yyyy-mm-dd&quot AS Expr1
FROM Alarmer_Jan04
WHERE ((( Format(Alarmer_Jan04.[Create Date],"yyyy-mm-dd&quot = "2004-01-09&quot)
GROUP BY Alarmer_Jan04.[MC-AlarmDescr], Alarmer_Jan04.AlarmTrapTime, Alarmer_Jan04.AckTIME, Alarmer_Jan04.ResolvedTIME;


But this doesn't work "Syntax error (missing operator)