INSERT date format - help!

[georgio]

Oracle does not like inserting 2000-12-01 00:00:00.1 into a date field. Error is ORA-01861: literal does not match format string. But this is the format it returns in a result set on existing date data. Does anyone know do I insert a DATE into a DATE field of Oracle 8 using SQL?

Thanks,
Chris

insert into UPS_OUT (CUSTOMER_IDENTIFIER, DATE_ORDERED, ORDER_KEY, SERVICE_TYPE, BILLING_OPTION, NOTIFY_OPTION, NOTIFY_TYPE, NOTIFY_EMAIL, SHIP_NAME, SHIP_STREET, SHIP_CITY, SHIP_STATE, SHIP_ZIP, SHIP_COUNTRY, PACKAGE_TYPE) values ('" + identifier + "','2000-12-01 00:00:00.0','" + orderKey + "','G','PP','Y','E','" + email + "','" + name + "','" + shipStreet + "','" + shipCity + "','" + shipState + "','" + shipZip + "','" + "US" + "','PKG')

 

[saltydog]

to_date('2000-12-01 00:00:00','YYYY-MM-DD HH24:MI:SS')

 

[Guest_imported]

Do I place to_date as a parameter in the prepared statement. It may be important to know that I am connecting to the db with java. The prepared statement is executed over an sql connection.

Thanks for the quick response.

 

[RandyD]

insert into UPS_OUT (CUSTOMER_IDENTIFIER, DATE_ORDERED, ORDER_KEY, SERVICE_TYPE, BILLING_OPTION, NOTIFY_OPTION, NOTIFY_TYPE, NOTIFY_EMAIL, SHIP_NAME, SHIP_STREET, SHIP_CITY, SHIP_STATE, SHIP_ZIP, SHIP_COUNTRY, PACKAGE_TYPE) values ('" + identifier + "',to_date('2000-12-01 00:00:00','YYYY-MM-DD HH24:MI:SS') ,'" + orderKey + "','G','PP','Y','E','" + email + "','" + name + "','" + shipStreet + "','" + shipCity + "','" + shipState + "','" + shipZip + "','" + "US" + "','PKG')

 

[AgentM]

I have a date issue here,
I am using ASP to insert date in Oracle my insert stmt is
sql="Insert into dummy (id,tdate) values (id.nextval,
to_date('" & tdate & "','mm/dd/yy') "

somwhere in ASP code
tdate = Formatdatetime(Now,2)

The code inserts data in the database but when I retrieve it thru the field thru the asp code. It crashes.

My question is:
What's the best way to insert date in Oracle , tdate can also be a form value.

 

[THoey]

I can't help any on the ASP side, but I would try to narrow down the problem. Use your code to put a value into the table and then, using SQL*Plus, run a SELECT statement to see if the data loaded. If it was, you know that the error is in your display of the data. Are you using any format string when you pull the data out for display?

Sorry, this probably doesn't help much, but maybe it will trigger an idea in someone... Terry M. Hoey