Home » SQL & PL/SQL » SQL & PL/SQL » Date conversion error (Oracle 9i)
Date conversion error [message #351303] Tue, 30 September 2008 04:41 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have created a table:
CREATE TABLE CUSTOMER3
(
  CUSTOMER_ID    INTEGER NOT NULL,
  CUSTOMER_NAME  VARCHAR2(100),
  DOB            DATE
)


I created a procedure to insert records into this table.

CREATE OR REPLACE PROCEDURE INSERT_CUSTOMER2
   (
     p_CustomerId INTEGER,
     p_customername varchar2,
     p_dob  date
  )
  AS
  BEGIN
     INSERT INTO customer2 (customer_id, customer_name, dob) values (p_CustomerId, p_customername, p_dob);
 END ;
/


When I try to exec the procedure INSERT_CUSTOMER2 as:

SQL> exec insert_customer2('1','name10','12/12/2008');
BEGIN insert_customer2('1','name10','12/12/2008'); END;

                                         *
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 1


Please advice,

Vir
Re: Date conversion error [message #351304 is a reply to message #351303] Tue, 30 September 2008 04:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your column DOB is a DATE datatype.
The value '12/12/2008' is a VARCHAR2 datatype.

You need to read up on TO_DATE
Re: Date conversion error [message #351317 is a reply to message #351303] Tue, 30 September 2008 05:12 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi JRow,
The procedure is correct ...the way of executing was wrong.
I ran the below command and it executed.

exec insert_customer2('11','name1',TO_CHAR('29-sep-2008'));


I want to insert the date "29-sep-2008" in format "dd/mm/yyyy" that is "29/09/2008".

I want to execute the above procedure similar as below statemet :
SQL> select TO_CHAR(TO_DATE('29-sep-2008'),'mm/dd/yyyy') from dual;

TO_CHAR(TO
----------
09/29/2008


I want to change the format of below statement's date in above format:
exec insert_customer2('11','name1',TO_CHAR('29-sep-2008'));


I tried many ways but not getting the result :
SQL> exec insert_customer2('13','name13',TO_DATE('29-sep-2008','mm/dd/yyyy'));
BEGIN insert_customer2('13','name13',TO_DATE('29-sep-2008','mm/dd/yyyy')); END;

       *
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 1



Please help me on this.

Regards,
Vir
Re: Date conversion error [message #351321 is a reply to message #351317] Tue, 30 September 2008 05:22 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
 TO_DATE('29-sep-2008','mm/dd/yyyy'))


Wrong.

You need to clear your basics.

Regards,
Rajat
Re: Date conversion error [message #351322 is a reply to message #351321] Tue, 30 September 2008 05:24 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
exec insert_customer2('15','name13',TO_DATE('29-sep-2008','dd/mm/yyyy'));


This worked.

Re: Date conversion error [message #351326 is a reply to message #351322] Tue, 30 September 2008 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
virmahi wrote on Tue, 30 September 2008 12:24
exec insert_customer2('15','name13',TO_DATE('29-sep-2008','dd/mm/yyyy'));


This worked.

Thanks to Oracle to be smarter than developer.

Regards
Michel

Re: Date conversion error [message #351352 is a reply to message #351322] Tue, 30 September 2008 08:10 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
virmahi wrote on Tue, 30 September 2008 06:24
exec insert_customer2('15','name13',TO_DATE('29-sep-2008','dd/mm/yyyy'));




This may have worked, but you also are relying on implicit conversion for the your first parameter. Why don't you code correctly to avoid these mistakes?
Previous Topic: :NEW in trigger
Next Topic: help in SQL
Goto Forum:
  


Current Time: Fri Dec 09 23:12:03 CST 2016

Total time taken to generate the page: 0.05549 seconds