Not a Valid Month – Working with Dates in Oracle

I am finding it strenuous to work with dates in my customized environment. I have a request to add a where clause which caters to specific dates but I just cannot get oracle to budge. Any ideas anyone please.

 select created_date, cast(created_date as date) as created_date_cast
   from mytable;

 created_date                                  created_date_cast
 04-Mar-20 05.21.15.772000 AM                  3/4/2020 5:21:15 AM
 04-Mar-20 05.21.15.709000 AM                  3/4/2020 5:21:15 AM
 04-Mar-20 05.17.14.902000 AM                  3/4/2020 5:14:14 AM
 28-Feb-20 01.15.25.702700 AM                  2/28/2020 1:15:25 AM

When I try to add a where clause the snippet blows up with the error:

select created_date, cast(created_date as date) as created_date_cast
  from mytable
 where cast(created_date as date) <= '02/28/2020';

ORA-01843: not a valid month

I have also tried to_date(created_date, 'MM/DD/YYYY') in the from but proves to be erroneous with:

ORA-01858: a non-numeric character was found where a numeric was expected

Answer

Firstly cast as date which converts a timestamp value to a date value, and then don’t forget to add trunc() function in order to include the boundry value (date'2020-02-28' in this case) also as

where trunc(cast(created_date as date)) <= date'2020-02-28'

Demo

Advertisement

Answer