group by function not working fine with datetime

Question

Hi i need group the records based on the time stmap

datetime column
2011-11-23 06:08:50.000
2011-11-23 06:08:50.000
2011-11-23 06:21:06.000
2011-11-23 06:21:06.000
2011-11-23 06:21:06.000
2011-11-23 07:00:18.000

right now it displays like this when i do group by function

2011-11-23 06:08:50.000    2
2011-11-23 06:21:06.000    3
2011-11-23 07:00:18.000    1

ar per my result i need to get
2011-11-23 6

Duplicate of [Best approach to remove time part of datetime in SQL Server](http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server) because the question is about removing time from datetime. Not GROUP BY — gbn, Jan 31 '12 at 12:13

score 2 · Answer 1 · answered Jan 31 '12 at 12:14

2

In 2008;

select cast(f as date), count(*) 
from t
group by cast(f as date)

answered Jan 31 '12 at 12:14

Alex K.

171,639
30
264
288

score 1 · Accepted Answer · answered Jan 31 '12 at 12:12

1

Try this:

Group by CONVERT(varchar(13), dateTimeColumn, 121)

This converts the time to a string matching pattern YYYY-MM-DD HH, which seems to be what you want, e.g. '2012-01-31 12'.

That format will also sort and compare in the natural way.

For a resolution of days, use varchar(10), hours varchar(13), minutes, use varchar(16), seconds varchar(19). Essentially you are truncating the string at the relevant point.

answered Jan 31 '12 at 12:12

Ben

34,935
6
74
113

There are better ways, http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server/1177529#1177529 – gbn Jan 31 '12 at 12:13
@gbn, there are more ***correct*** ways, but this one is extremely easy to use, to read, and to remember. – Ben Jan 31 '12 at 12:34
I dunno, a simply CAST to date (as per @Alex K.) is quite clean. Anyway, it is important if you aggregated over a million rows, or have to cast back to datetime – gbn Jan 31 '12 at 12:36

score 0 · Answer 3 · answered Jan 31 '12 at 12:12

0

select Cast(Floor(Cast(DateTimeColumn as float))as datetime), Count(*)
From Yourtable
Group By Cast(Floor(Cast(DateTimeColumn as float))as datetime)

answered Jan 31 '12 at 12:12

codingbadger

42,678
13
95
110

score 0 · Answer 4 · answered Jan 31 '12 at 12:16

0

I would do

group by day(date_column), month(date_column), year(date_column)

you can later do (with the proper casting) on your select:

select  day(date_column)+ '\' + month(date_column) + '\' +  year(date_column)

answered Jan 31 '12 at 12:16

Diego

34,802
21
91
134

group by function not working fine with datetime

4 Answers4