Date data type [message #358150] |
Sun, 09 November 2008 09:15  |
meo_beo
Messages: 12 Registered: September 2008
|
Junior Member |
|
|
Hi, can anyone please tell me the format of the Date data type of Oracle 10g. I've been unsuccessfully trying to insert a row into a table because of the wrong format. My SQL statement is as follows:
INSERT INTO bco_orders (order_id, order_date, cus_id) VALUES (1, '1/1/2008', 1);
The "order_date" column is of Date data type, I also tried:
INSERT INTO bco_orders (order_id, order_date, cus_id) VALUES (1, '1-1-2008', 1);
but it all came up with the same error message:
ORA-01843: not a valid month
Can anyone tell me what the problem is, thanks a lot!
|
|
|
|
Re: Date data type [message #358160 is a reply to message #358150] |
Sun, 09 November 2008 15:21   |
samtajain
Messages: 7 Registered: November 2008
|
Junior Member |
|
|
There is a defualt value set for DATE FORMAT against the parameter(NLS_DATE_FORMAT):
You can check it through
SELECT * FROM NLS_SESSION_PARAMETERS;
or through
SELECT * FROM V$NLS_PARAMETERS;
Check the default format which is set and you can directly enter it in that format Ex. if its set as DD-MON-RR then you can directly put the insert statement like:
INSERT INTO bco_orders (order_id, order_date, cus_id) VALUES (1, '1-JAN-2008', 1);
But as a best practice you should use TO_DATE as:
INSERT INTO bco_orders (order_id, order_date, cus_id) VALUES (1, TO_DATE('1/1/2008','DD/MM/YYYY'), 1);
Hope it helps..
[Updated on: Sun, 09 November 2008 15:22] Report message to a moderator
|
|
|
Re: Date data type [message #358161 is a reply to message #358150] |
Sun, 09 November 2008 18:31   |
dr.s.raghunathan
Messages: 540 Registered: February 2008
|
Senior Member |
|
|
hi,
in general and by dafault you shall give the data as dd/mon/yyyy and error will go in 10G unless you have already specified some date format through nls_date_format in session.
yours
dr.s.raghunathan
|
|
|
Re: Date data type [message #358216 is a reply to message #358161] |
Mon, 10 November 2008 02:49   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I disagree.
In general, and by default you should specify a format mask for your date conversions.
Relying on a default date mask is sloppy and causes unneccessary problems.
|
|
|
|