Introduction
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')
Since 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 trunc(es_date)
.
TIP: You can create and index on trunc(es_date)
if that is something you
are doing on a regular basis.
The literal '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 'dd-MON-yy'
.
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
e.g. 'dd-mm-yyyy'
or 'yyyy-mm-dd'
will avoid problems when switching to different language.