CODEWITHSUNDEEP
sqloracle-databasetimestamp
Haha
Haha

Reputation: 1009

Why is Oracle returning a Not Valid Month error

Can someone tell me why is this SQL query returning a 

ORA-01843. 00000 -  "not a valid month"

I now it is wrong but it should be because 2018 is not a valid day. 09 is a valid month. I think..

select to_timestamp('2018-09-05 11:35:41', 'dd/MM/yyyy HH:mi:ss') from dual;

I know that the query is wrong. I just want to know why it isn't saying not a valid day or something like that. the error is now saying that the month is wrong which is false.

Upvotes: 2

Views: 397

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

If you "insist" to get an error message related to day then try the FX modifier:

SELECT TO_TIMESTAMP('2018-09-05 11:35:41', 'FXdd/MM/yyyy HH:mi:ss') FROM dual;

Error at line 1
ORA-01861: literal does not match format string

I assume that is the error message you would expect.

Or try a "valid" month, e.g. 

SELECT TO_TIMESTAMP('2008-09-05 11:35:41', 'dd/MM/yyyy HH:mi:ss') FROM dual;

Error at line 1
ORA-01830: date format picture ends before converting entire input string

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The reason is because Oracle is trying to be clever/helpful. So, it is interpreting:

select to_timestamp('2018-09-05 11:35:41', 'dd/MM/yyyy HH:mi:ss') from dual;
---------------------^ddMM

The 20 is interpreted as a valid day. The month then follows. Oracle is helpfully trying to ignore the separator. Hence, the 18 is an invalid month.

Try this:

select to_timestamp('2012-2012', 'dd/MM/yyyy') from dual

Upvotes: 6

Related Questions