Oracle SQL date format can be tricky to understand and could make you loose time to figure out why a query is not working. In this post we explain how to query a timestamp column by using a date.
Filter by date using truncate
Suppose that your table has a column called CREATED_AT with TIMESTAMP(6) type, which has a row the following valuein the created_at column:
- 30 December 2017 13.04.08.654030000 AM
So you have a date and you want to filter the date in a timestamp column, the query should look like this one:
select * from table where TRUNCATE(created_at)=TO_DATE('2017-12-30','yyyy-mm-dd')
to_date() returns a date at 00:00:00, this will filter out row since it doesn't match on minutes, hours and seconds.
To save the issue with hours, minutes and seconds we use
TIP: You can create and index on
trunc(es_date) if that is something you
are doing on a regular basis.
'27-APR-12' can fail very easily if the default date format is changed to anything different. So make sure you
you always use
to_date() with a proper format mask (or an ANSI literal:
date '2012-04-27') Although you did right in using
to_date() and not
relying on implict data type conversion, your usage of to_date() still has a
subtle pitfall because of the format
Why we choose yyyy-mm-dd as format?
You should avoid anything in the format that might be different in a different language. Using a four digit year and only numbers
'yyyy-mm-dd' will avoid problems when switching to different language.