CODEWITHSUNDEEP
sqloracle-database
Jake Wagner
Jake Wagner

Reputation: 826

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

Upvotes: 0

Views: 180

Answers (3)

MT0
MT0

Reputation: 167774

Don't use CAST and don't use TRUNC (as then Oracle will not be able to use an index on your column but would, instead, require a function-based index created on TRUNC(created_date)) just add a day and use a literal:

SELECT created_date
FROM   mytable
WHERE  created_date < DATE '2020-02-29';

or

SELECT created_date
FROM   mytable
WHERE  created_date < TIMESTAMP '2020-02-29 00:00:00';

or, if you want to specify the exact date then just add a day. E.g.:

SELECT created_date
FROM   mytable
WHERE  created_date < DATE '2020-02-28' + INTERVAL '1' DAY;

All of those options should be able to use an index on the created_date column.

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

TO_DATE( value_string, format_model ) takes strings as its arguments but CREATED_DATE is a TIMESTAMP data type and not a string so Oracle must make an implicit TIMESTAMP-to-string conversion and it does this using the NLS_TIMESTAMP_FORMAT session parameter; so your expression is effectively:

TO_DATE(
  TO_CHAR(
    created_date,
    ( SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_TIMESTAMP_FORMAT' )
  ),
  'MM/DD/YYYY'
)

And if your NLS_TIMESTAMP_FORMAT is not MM/DD/YYYY then its highly likely that an exception will be raised (i.e. like the ORA-01858 you had).

You should never rely on implicit string conversions as any user can change their own session parameters at any time and an implicit conversion that works for one user may not work for another just because they have different parameter values (even though the queries are identical).

Upvotes: 1

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65105

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use a date literal:

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

I would also recommend dispensing with the cast() -- assuming that created_date is correctly stored as a date or timestamp:

created_date < (date '2020-02-28' + interval '1' day)

Upvotes: 0

Related Questions