CODEWITHSUNDEEP
oracle-database
Amatador-alias
Amatador-alias

Reputation: 1

How can I fix this error 'ORA-01843: not a valid month'

I am trying to create a table using the followning query: 

create table customer1
(
custmer_id number, 
Customer_name varchar(20), 
Expiry_date  date default TO_DATE ('31-12-2009','dd-mon-RR')
)

the result is the following error : 

Error report -
ORA-01843: not a valid month
01843. 00000 -  "not a valid month"
*Cause:    
*Action:

Anyone to give me a light regarding this. Thank you.

Upvotes: 0

Views: 24403

Answers (2)

MT0
MT0

Reputation: 168806

[TL;DR] Use a date literal:

CREATE TABLE customer1 (
  custmer_id    NUMBER, 
  Customer_name VARCHAR2(20), 
  Expiry_date   DATE        DEFAULT DATE '2009-12-31'
)

or the MM format model:

CREATE TABLE customer1 (
  custmer_id    NUMBER, 
  Customer_name VARCHAR2(20), 
  Expiry_date   DATE        DEFAULT TO_DATE ('31-12-2009','DD-MM-RRRR')
)

From Oracle's Datetime Format Models:

  • MM: Month (01-12; January = 01).
  • MON: Abbreviated name of month.
  • MONTH: Name of month, padded with blanks to display width of the widest name of month in the date language used for this element.

Oracle's String-to-Date Conversion Rules allow additional formatting rules (without any other modifiers being applied). So:

  • MM also matches MON and MONTH;
  • MON matches MONTH (and vice versa);
  • RR matches RRRR; and
  • The punctuation is optional.

The format model dd-mon-RR you are using can match any of (with or without the hyphens):

  • 31-dec-09
  • 31-december-09
  • 31-dec-2009
  • 31-december-2009

But the MON format model does not also match the MM format so cannot match your string.

Instead, you should use the DD-MM-RR (or DD-MM-RRRR) format model which could match (again, with or without hyphens):

  • 31-12-09
  • 31-dec-09
  • 31-december-09
  • 31-12-2009
  • 31-dec-2009
  • 31-december-2009

If you want to only match a single date format then either:

  • Use a date literal DATE '2009-12-31' (which only uses the ISO-8601 date format so skips the issue of your input not matching the format model); or
  • Use the "format exact" FX format model modifier TO_DATE( '31-12-2009', 'FXDD-MM-RRRR' ) which will prevent the String-to-Date conversion rules being applied and require the input to exactly match the format model.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 143163

Wrong format mask. Neither is 12 = mon (it is mm), nor is 2009 = RR (it is RRRR).

Switch to 

create table customer1
(
custmer_id number, 
Customer_name varchar(20), 
Expiry_date  date default TO_DATE ('31-12-2009','dd-mm-RRRR')
)

Upvotes: 2

Related Questions