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?

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