CODEWITHSUNDEEP
dateoracle11g
Lora
Lora

Reputation: 15

ORA-01843: not a valid month - but what month format? Oracle 11g

I want to know what other MONTH formats exist except MM , MONTH or MON. When the query below runs it gives me an error ORA-01843: not a valid month and I can understand why, because the server removes the "0" from the month "07" and leaves only the number "7", so the MM format is not the right one. But which one is it?

select to_char(to_date(START_DATE,'MM/DD/YYYY '), 'DD-MM-YYYY')
from PER_ALL_PEOPLE_F
WHERE person_id=12345

The START_DATE column is DATE TYPE and it provides results like: 7/17/2012 .

Upvotes: 1

Views: 7893

Answers (1)

Alex Poole
Alex Poole

Reputation: 191425

Your assumption that the single-digit 7 for the month is a problem is not correct; Oracle is generally quite flexible and will happily parse a single digit month with the MM model:

select to_date('7/17/2012', 'MM/DD/YYYY') from dual;

TO_DATE('7/17/2012'
-------------------
2012-07-17 00:00:00

If start_date is already a DATE type then you should not be calling to_date() for it. You're doing an implicit conversion to a string using your NLS_DATE_FORMAT moodel, and then back to a date with your specified format. So really you're doing:

select to_char(to_date(to_char(START_DATE, <NLS_DATE_FORMAT>),
   'MM/DD/YYYY '), 'DD-MM-YYYY')

If your NLS_DATE_FORMAT is something other than MM/DD/YYYY, e.g. DD-MON-YYYY, then you'll get an ORA-1843 error for at least some values.

You can see this with:

select to_date(date '2014-01-16', 'MM/DD/YYYY') from dual;

or the expanded:

select to_date(to_char(date '2014-01-16', 'DD-MON-YYYY'),
  'MM/DD/YYYY') from dual;

Dates do not have any specific format, they're stored in an internal representation and then converted to a formatted string for display. You said your dates display like 7/12/2012, but given the error you're seeing your client seems to be doign that formatting, and it isn't related to the session NLS_DATE_FORMAT.

You only need to do:

select to_char(START_DATE, 'DD-MM-YYYY')

Upvotes: 3

Related Questions