oracle to_date function not accepting format

Question

When I do this in oracle 10g:

select to_date(trunc(SOMEINPUTdATE)) from table1
where to_date(trunc(date_column_timestamp),'MM/DD/YYYY')
  >= to_date('03/11/2011' ,'MM/DD/YYYY')

I get: ORA-01843: not a valid month if I change to : 'YYYY/MM/DD', it works. But 'MM/DD/YYYY' is a valid format right?

Answer 1

You're getting things mixed up a bit. TO_DATE converts a string into a DATE. If date_column_timestamp is already a date, you don't need to convert it to a date.

select trunc(SOMEINPUTdATE) from table1
where trunc(date_column_timestamp)
  >= to_date('03/11/2011' ,'MM/DD/YYYY')

The ORA-01843 is caused by the implicit conversion of a date to string. In other words, the following:

to_date(trunc(date_column_timestamp),'MM/DD/YYYY')

is equivalent to (assuming the default date format DD-MON-YYYY):

to_date(TO_CHAR(trunc(date_column_timestamp),'DD-MON-YYYY'),'MM/DD/YYYY')

So, the TO_CHAR returns something like '11-MAR-2011', which then causes to_date to fail because the date formats do not match.

The same problem exists in your select clause. You don't need to_date around a trunc of a date column.