Group by date without time

Question

I was wondering if there is 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 simply groups them by a unique date.

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

Is there any cast or something that I can do to make these 2 dates above appear as a single row with a total?

Answer 1

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

Answer 2

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 later. For older versions of SQL, you can use some clever manipulations as follows:

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))