308

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

1

9 Answers 9

Reset to default
518

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

GROUP BY CAST(myDateTime AS DATE)
8
  • 3
    do you know how I would do the same with LINQ to SQL?
    – The Muffin Man
    Commented May 19, 2011 at 5:56
  • 1
    @Nick - not sure. Try using DateTime.Date.
    – Oded
    Commented May 19, 2011 at 6:03
  • 3
    - Linq to Sql : DateTime.Date - Entity Framework: EntityFunctions.TruncateTime(myDateTime)
    – The Muffin Man
    Commented Jul 5, 2012 at 22:47
  • 1
    I agree, I was responding to The Muffin Man who was asking about it in an ORM context.
    – Niels Brinch
    Commented Feb 2, 2015 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
    Commented Apr 12, 2016 at 6:57
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)
0
18

CAST datetime field to date

select  CAST(datetime_field as DATE), count(*) as count from table group by CAST(datetime_field as DATE);
2
  • 1
    Repeats the accepted answer. Please don't re-post answers. Instead, vote for answers that helped you.
    – Gert Arnold
    Commented Jul 19, 2022 at 17:15
  • 2
    @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
    Commented Aug 31, 2022 at 4:48
17

In pre Sql 2008 By taking out the date part: 

GROUP BY CONVERT(CHAR(8),DateTimeColumn,10)
1
6 
GROUP BY DATE(date_time_column)
1
  • There is no such date function in SQL Server
    – Martin Smith
    Commented Oct 3, 2023 at 7: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)
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_
    Commented Mar 2, 2016 at 16:25
1

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');
0

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.

1
  • 2
    Repeats the accepted answer. Please don't re-post answers. Instead, vote for answers that helped you.
    – Gert Arnold
    Commented Jul 19, 2022 at 17:15
0

From SQL Server 2022 you can use DATETRUNC.

If your datetime column is indexed this can make better use of the order provided by it than earlier alternatives.

DECLARE @Demo TABLE
(
myDateTime DATETIME INDEX ix_myDateTime
)

SELECT DATETRUNC(DAY, myDateTime) AS Date,
       COUNT(*)
FROM @Demo
GROUP BY DATETRUNC(DAY, myDateTime)


SELECT CAST(myDateTime AS DATE) AS Date,
       COUNT(*)
FROM @Demo
GROUP BY CAST(myDateTime AS DATE)

enter image description here

Not the answer you're looking for? Browse other questions tagged or ask your own question.