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