Group by date without time

Question

I was wondering whether there was a way to group dates that are 2014-01-26 05:39:29.000 and 2014-01-26 07:45:31.000 into one day when counting them. I currently have the following code that just groups them by their unique datetime.

    SELECT ETK_ExpirationDateTime, COUNT(*) as TotalRows
    FROM History_Action 
    WHERE [State] = 4
    GROUP BY ETK_ExpirationDateTime
    ORDER BY ETK_ExpirationDateTime

Is there a cast or something I can do to make those 2 dates above appear as one row with a total sum?

possible duplicate of [How can I group by date time column without taking time into consideration](http://stackoverflow.com/questions/6054144/how-can-i-group-by-date-time-column-without-taking-time-into-consideration) — imtheman, Jun 22 '15 at 19:42
^ I tried using that originally but was unable to figure out how to do it when you were filtering out a certain column. — user3712641, Jun 22 '15 at 19:44

score 21 · Answer 1 · answered Jun 22 '15 at 19:42

21

SELECT CAST(ETK_ExpirationDateTime AS DATE) AS DATE, COUNT(*) as TotalRows
FROM History_Action 
WHERE [State] = 4
GROUP BY CAST(ETK_ExpirationDateTime AS DATE)
ORDER BY 1

answered Jun 22 '15 at 19:42

KrazzyNefarious

3,202
3
20
32

score 5 · Answer 2 · answered Jun 22 '15 at 19:42

You can use conversion to date:

SELECT CONVERT(date, ETK_ExpirationDateTime) as ExpirationDateTime, COUNT(*) as TotalRows
FROM History_Action 
WHERE [State] = 4
GROUP BY CONVERT(date, ETK_ExpirationDateTime)
ORDER BY CONVERT(date, ETK_ExpirationDateTime)

This only works for SQL 2008 or newer. For older versions of SQL you can use some tricky manipulation like this:

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, ETK_ExpirationDateTime)) as ExpirationDateTime, COUNT(*) as TotalRows
FROM History_Action 
WHERE [State] = 4
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, ETK_ExpirationDateTime))
ORDER BY DATEADD(dd, 0, DATEDIFF(dd, 0, ETK_ExpirationDateTime))

Yes but please [don't use lazy shorthand like `dd`](https://sqlblog.org/blogs/aaron_bertrand/archive/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations.aspx). — Aaron Bertrand, Jun 22 '15 at 20:17

Group by date without time

2 Answers2

Linked