Informix SQL: How to get the date part of a datetime field in a query?

sql date datetime informix
weismat picture weismat · Nov 14, 2010 · Viewed 31.6k times · Source

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.

Answer

Jonathan Leffler picture Jonathan Leffler · Nov 14, 2010

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.

Related questions

How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
Best approach to remove time part of datetime in SQL Server

Which method provides the best performance when removing the time portion from a datetime field in SQL Server? a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) or b) select cast(convert(char(11), getdate(), 113) as datetime) The second method does send a …

Read More
MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More