Home » SQL & PL/SQL » SQL & PL/SQL » Date data type
Date data type [message #358150] Sun, 09 November 2008 09:15 Go to next message
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 #358151 is a reply to message #358150] Sun, 09 November 2008 09:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should start to read SQL Reference, especially:
- Chapter 2 "Basic Elements of Oracle SQL", section "Datatypes", paragraph DATE Datatype
- TO_DATE function
- Datetime Format Models

Regards
Michel

Re: Date data type [message #358160 is a reply to message #358150] Sun, 09 November 2008 15:21 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Date data type [message #358274 is a reply to message #358216] Mon, 10 November 2008 08:13 Go to previous message
meo_beo
Messages: 12
Registered: September 2008
Junior Member
Thank you, guys. You've been most helpful. Smile

[Updated on: Mon, 10 November 2008 08:14]

Report message to a moderator

Previous Topic: Date with suffix in top
Next Topic: nested views with hints
Goto Forum:
  


Current Time: Tue Dec 06 02:23:38 CST 2016

Total time taken to generate the page: 0.08735 seconds