Reputation: 1
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
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
; andThe 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:
DATE '2009-12-31'
(which only uses the ISO-8601 date format so skips the issue of your input not matching the format model); orFX
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
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