What is the best way to use a only the date part of a datetime field in a query?
I have a datetime field and want to group/count it by date.
In SQL Server 2008 and above, we can either use the CONVERT or CAST function to return the DATE part from the DATETIME datatype.
There are a number of ways of doing it:
EXTEND(dt_field, YEAR TO DAY)
CAST(dt_field AS DATETIME YEAR TO DAY)
dt_field::DATETIME YEAR TO DAY
CAST(dt_field AS DATE)
dt_field::DATE
DATE(dt_field)
The simplest - as in shortest - are the last two, and the function notation is probably clearest. The first three leave you with a DATETIME value; the last three leave you with a DATE value. These are similar, but not identical. They are fairly freely interchangeable though.
date(name_of_field)
This
to_char(dt_field, "%d %B %Y")
gives as 31 May 2016
.
To group:
SELECT YEAR(dt_field) year, MONTH(dt_field) month, COUNT(*) count
FROM tblName
GROUP BY 1, 2
ORDER BY 1, 2;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With