How can I group by date time column without taking time into consideration

Question

I have a bunch of product orders and I'm trying to group by the date and sum the quantity for that date. How can I group by the month/day/year without taking the time part into consideration?

3/8/2010 7:42:00 should be grouped with 3/8/2010 4:15:00

See also similar [group per hour](http://stackoverflow.com/questions/7001718/sql-server-group-by-count-of-datetime-per-hour) — Michael Freidgeim, May 16 '13 at 21:16

score 501 · Accepted Answer · edited Jun 01 '12 at 18:43

501

Cast/Convert the values to a Date type for your group by.

GROUP BY CAST(myDateTime AS DATE)

edited Jun 01 '12 at 18:43

AlG

14,697
4
41
54

answered May 19 '11 at 05:26

Oded

489,969
99
883
1,009

Nice one. If this weren't 2008 with the DATE type, it would be a bit more messy but this works well. – IAmTimCorey May 19 '11 at 05:29
3

do you know how I would do the same with LINQ to SQL? – The Muffin Man May 19 '11 at 05:56
1

@Nick - not sure. Try using `DateTime.Date`. – Oded May 19 '11 at 06:03
3

- Linq to Sql : `DateTime.Date` - Entity Framework: `EntityFunctions.TruncateTime(myDateTime)` – The Muffin Man Jul 05 '12 at 22:47
Combining DateTime.Year and DateTime.Month and DateTime.Day actually works in Entity Framework AND it works in LINQ to Objects too, which makes it a better choice in my opinion. – Niels Brinch Feb 02 '15 at 14:28
@NielsBrinch - this is pure SQL, without an ORM. Not sure your point is valid when an ORM is not involved. – Oded Feb 02 '15 at 14:46
1

I agree, I was responding to The Muffin Man who was asking about it in an ORM context. – Niels Brinch Feb 02 '15 at 18:48
7

Thanks a lot...it solved my problem. added ' group by CAST(date_modified AS DATE)' . don't forget to add the same( CAST(date_modified AS DATE) ) in select cluase. – Mohammed mansoor Apr 12 '16 at 06:57

Mitch Wheat · Answer 2 · 2014-04-08T00:14:57.057

32

GROUP BY DATEADD(day, DATEDIFF(day, 0, MyDateTimeColumn), 0)

Or in SQL Server 2008 onwards you could simply cast to Date as @Oded suggested:

GROUP BY CAST(orderDate AS DATE)

edited Apr 08 '14 at 00:14

answered May 19 '11 at 05:25

Mitch Wheat

295,962
43
465
541

Kamyar · Answer 3 · 2012-10-15T05:12:10.263

17

In pre Sql 2008 By taking out the date part:

GROUP BY CONVERT(CHAR(8),DateTimeColumn,10)

edited Oct 15 '12 at 05:12

answered May 19 '11 at 05:27

Kamyar

18,639
9
97
171

2

For explanation about the convert function and date time type (meaning of char(8) and 10), see http://www.w3schools.com/sql/func_convert.asp – super1ha1 Dec 16 '16 at 03:18

score 11 · Answer 4 · answered May 18 '20 at 06:33

11

CAST datetime field to date

select  CAST(datetime_field as DATE), count(*) as count from table group by CAST(datetime_field as DATE);

answered May 18 '20 at 06:33

Ruchira Nawarathna

1,137
17
30

Repeats the [accepted answer](https://stackoverflow.com/a/6054176/861716). Please don't re-post answers. Instead, vote for answers that helped you. – Gert Arnold Jul 19 '22 at 17:15
@GertArnold it's not the same -- the accepted answer only recommends adding CAST to the GROUP BY clause, while this answer recommends adding it to the SELECT as well – M.M Aug 31 '22 at 04:48

score 8 · Answer 5 · answered Jul 02 '18 at 08:23

8

GROUP BY DATE(date_time_column)

answered Jul 02 '18 at 08:23

Aravindh Gopi

2,083
28
36

score 2 · Answer 6 · edited Jan 07 '14 at 16:01

2

Here's an example that I used when I needed to count the number of records for a particular date without the time portion:

select count(convert(CHAR(10), dtcreatedate, 103) ),convert(char(10), dtcreatedate, 103)
FROM dbo.tbltobecounted
GROUP BY CONVERT(CHAR(10),dtcreatedate,103)
ORDER BY CONVERT(CHAR(10),dtcreatedate,103)

edited Jan 07 '14 at 16:01

Robert

5,278
43
65
115

answered Jan 07 '14 at 15:42

user3169774

21
1

2

The ORDER BY will not work as supposed because it will not treat it as date so it will sort by day – Ed_ Mar 02 '16 at 16:25

score 2 · Answer 7 · answered Jul 04 '17 at 05:18

2

Here is the example works fine in oracle

select to_char(columnname, 'DD/MON/yyyy'), count(*) from table_name group by to_char(createddate, 'DD/MON/yyyy');

answered Jul 04 '17 at 05:18

Radhakrishnan

564
4
6

score 1 · Answer 8 · edited Aug 07 '21 at 23:24

1

Well, for me it was pretty much straight, I used cast with groupby:

Example:

Select cast(created_at as date), count(1) from dbname.tablename GROUP BY cast(created_at as date)

Note: I am using this on MSSQL 2016.

edited Aug 07 '21 at 23:24

halfer

19,824
17
99
186

answered Jul 20 '21 at 15:51

Vikash Pandey

5,407
6
41
42

1

Repeats the [accepted answer](https://stackoverflow.com/a/6054176/861716). Please don't re-post answers. Instead, vote for answers that helped you. – Gert Arnold Jul 19 '22 at 17:15

al1r3za__a · Answer 9 · 2020-01-17T18:41:15.930

-1

I believe you need to group by , in that day of the month of the year . so why not using TRUNK_DATE functions . The way it works is described below :

Group By DATE_TRUNC('day' , 'occurred_at_time')

edited Jan 17 '20 at 18:41

answered Jan 17 '20 at 18:35

al1r3za__a

1
2

1

date_trunc is for PostgreSQL not SQL Server which going by the tags, the OP was seeking a solution for SQL server. – Dave Hogan Jun 01 '20 at 15:44

How can I group by date time column without taking time into consideration

9 Answers9

Linked

Related