You Asked

Hi,

Am getting an error like.

ORA-01843 not a valid month..

My stat like...

CASE when
To_date(column, 'mm/dd/yyyy')>= systdate then 'active '
Else 'inactive '
timezoneconverter(col)

Like

I seen Nls date format also, it is
DD mm yyyy format...that I converted to mm dd yyyy format..

But still am getting same error like not a valid month.

Can you provide rght sol. Thanks.

and Connor said...

What data type is "column"

The most common error here is that COLUMN is already a date, so when you do

to_date(column)

we silently convert the column BACK to a string, and then try to perform a TO_DATE on it.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Brian

A reader, November 12, 2024 - 1:43 am UTC

Apparently, here timestamp having Year as 0000, 0001..

How do we handle this when it comes to search and to_Timestamp
?

Any idea How to fix this error. Thanks!

1)
SQL> update bt.RELATED_MKET set eff_dt_start=TO_TIMESTAMP('01-JAN-0001 05:00:00:000000', 'DD-Mon-YYYY HH24:MI:SS:FF')where eff_dt_start='01-01-00 06:00:00.000000000 AM';
update btgsm.RELATED_MARKETS set eff_dt_start=TO_TIMESTAMP('01-JAN-0001 05:00:00:000000', 'DD-Mon-YYYY HH24:MI:SS:FF')where eff_dt_start='01-01-00 06:00:00.000000000 AM'
*
ERROR at line 1:
ORA-01843: not a valid month

2)

SQL> select TO_TIMESTAMP('01-01-0000 6:00:00.000000', 'DD-MM-YYYY HH:MI:SS:FF') from dual ;
select TO_TIMESTAMP('01-01-0000 6:00:00.000000', 'DD-MM-YYYY HH:MI:SS:FF') from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

November 12, 2024 - 11:30 am UTC

The conversion of year 1 in the update works:

select TO_TIMESTAMP (
         '01-JAN-0001 05:00:00:000000', 
         'DD-Mon-YYYY HH24:MI:SS:FF' 
       )
from   dual;

TO_TIMESTAMP('01-JAN-000105:0
-----------------------------
01/01/0001 05:00:00.000000000

I'm guessing eff_dt_start a date rather than timestamp, so you have an implicit conversion which is the real source of the error. Try setting it to a date instead.

As the query says, year 0 is invalid in Oracle Database. This doesn't exist in the Gregorian calendar which goes from 31 Dec 1 BC -> 1 Jan 1 AD:

alter session set nls_date_format = 'DD MON YYYY BC';
select 
  to_date ( '31-Dec-0001 BC', 'DD-MON-YYYY BC') bc, 
  to_date ( '31-Dec-0001 BC', 'DD-MON-YYYY BC') + 1 ad
from dual

BC             AD            
-------------- --------------
31 DEC 0001 BC 01 JAN 0001 AD

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Brian

More to Explore

PL/SQL demos

PL/SQL docs