}

Oracle SQL: How to write a que for filterting Date columns

Created:

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.

sql