How to write date condition on where clause in oracle

Question

I have data in the date column as below.

reportDate

21-Jan-17 02-FEB-17

I want to write a query to fetch data for 01/21/2017?

Below query not working in Oracle.

SELECT * FROM tablename where reportDate=to_date('01/21/2017','mm/dd/yyyy')

Answer 1

What is the data type of reportDate? It may be DATE or VARCHAR2 and there is no way to know by just looking at it.

Run describe table_name (where table_name is the name of the table that contains this column) and see what it says.

If it's a VARCHAR2 then you need to convert it to a date as well. Use the proper format model: 'dd-Mon-rr'.

If it's DATE, it is possible it has time-of-day component; you could apply trunc() to it, but it is better to avoid calling functions on your columns if you can avoid it, for speed. In this case (if it's really DATE data type) the where condition should be

where report_date >= to_date('01/21/2017','mm/dd/yyyy') 
  and report_date <  to_date('01/21/2017','mm/dd/yyyy') + 1

Note that the date on the right-hand side can also be written, better, as

date '2017-01-21'

(this is the ANSI standard date literal, which requires the key word date and exactly the format shown, since it doesn't use a format model; use - as separator and the format yyyy-mm-dd.)

Answer 2

The query should be something like this

SELECT * 
FROM table_name 
WHERE TRUNC(column_name) = TO_DATE('21-JAN-17', 'DD-MON-RR');

The TRUNC function returns a date value specific to that column.

The o/p which I got when I executed in sqldeveloper https://i.stack.imgur.com/blDCw.png