How do I load the date field from the text file "10/20/2004 14:25" into oracle tables using sql loader ?
The contol file is as follows:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ORDER_DATE "DD/MM/YYYY HH:MM"
but it doesn't work, I also tried "DD/MMYYYY" with no luck, any suggestion ?
What is the datatype of the ORDER_DATE column. If it is a date you cannot insert only the Timestamp value alone. If it is a VARCHAR2/CHAR, you can extract the timestamp alone as below in SQLLDR control file
ORDER_DATE "TO_CHAR(TO_DATE(:ORDER_DATE,'DD/MM/YYYY HH24:MI'),'HH24:MI')"
the datatype is defined as date in oracle
actually, the data in this field looks like this:
10/11/2005 14:21 12/12/2005 12:00
10/11/2005 11:24 15/12/2005 13:30
12/11/2005 08:35 12:30
09/09/2005 09:45 10/12/2005 12:45
13/12/2005 15:20 18:30
18/12/2005 20:30 21:45
some data in the del_date_time only has a timestamp and some has both the date and the timestamp, i wish to use sql loader to convert and upload them into "dd/mm/yyyy hh:mi", if the data only contains a timestamp, i want the date to be the same as order_date_time
e.g. in row 3
12:30 will become 12/11/2005 12:30
This will help you to insert from the temp table to the main table
select to_date(order_date_time,'DD/MM/YYYY HH24:MI'),case when length(del_date_time) < 16 then to_char(to_date(order_date_time,'DD/MM/YYYY HH24:MI'),'DD/MM/YYYY') ||del_date_time