Last post May 23, 2011 04:25 PM by Lannie
May 19, 2011 11:09 PM|sbmdude|LINK
I have an Excel file (which has data imported from Oracle 10G Database) one of the fields is a Date Filed which has values like 28-JAN-11 03.25.11.000000000 PM ( Date field is Oracle Time Stamp(6) in Database )
When I am trying to Import the same Excel file from another Oracle 10 G database (for another database/application), I get an error because the data field is not being recognized by Oracle 10G --> Import is being done by ORACLE SQL Developer (Table (field)
has TIMESTAMP(6) as the datatype)
How can I import that field ?? For time being I made the TIMESTAMP to VARCHAR2 and its working but I could not convert that to Date field again in C# CODE ( it says not a valid date type).
May 23, 2011 11:45 AM|Lannie|LINK
Excel stores dates differently in the background from what is displayed in an opened worksheet (SERIAL DATE).
Excel stores the date-time as double number with 1.0 = 01-Jan-1900 00:00:00, 2.0 as 02-Jan-1900 00:00:00.
so for Oracle
you have to convert Excel date to string within Excel and use Oracle TO_DATE function as you have done
get oracle to load and convert using the Excel behind the scenes date storage rules.
May 23, 2011 03:24 PM|sbmdude|LINK
Hi thanks for the reply, yeah u said is right. Actuallly in SQL Developer tool there is an option through which we can change the foramt to the desired.
I have changed the FOMAT to "DD-MON-RR HH.MI.SS " and I get the required format now. Thanks ..!!!
May 23, 2011 04:25 PM|Lannie|LINK