Hi All,
I have a table called Product . I need to generate insert script which have different time format from a csv file .
Table : Product
create table product (prod_id number , prod_date date , prod_exp_date date , prod_sold_date date);
Input File:
PROD_ID PROD_DATE PROD_EXP_DATE PROD_SOLD_DATE
1 1/1/1998 1/1/4000 2002-03-14-17.17.47.402000
2 1/1/1998 1/1/4000 2001-03-14-17.17.47.420000
Query used in excel:
="INSERT INTO PRODUCT VALUES('"&A1&"','"&TEXT(B1,"MM-DD-YYYY")&"','"&TEXT(C1,"MM-DD-YYYY")&"','"&TEXT(D1,"DD-MM-YYYY HH:MM")&"');"
Insert script got :
INSERT INTO PRODUCT VALUES('1',' 1/1/1998 ','01-01-4000','2002-03-14-17.17.47.402000');
INSERT INTO PRODUCT VALUES('2',' 1/1/1998 ','01-01-4000','2001-03-14-17.17.47.420000');
Expected script output :
('1',to_date(' 1/1/1998','MM-DD-YYYY'), TO_DATE('01-01-4000','MM-DD-YYYY'),TO_TIMESTAMP(
'2002-03-14-17.17.47.402000','YYYY-MM-DD HH24:MI:SS:FF3');
('2',to_date(' 1/1/1998','MM-DD-YYYY'), TO_DATE('01-01-4000','MM-DD-YYYY'),TO_TIMESTAMP(
'2001-03-14-17.17.47.402000','YYYY-MM-DD HH24:MI:SS:FF2');
Since there are more entries i have to do more manual work . I also tried to import into a temp table and then insert into product table but when i inserted i got entries for date like '01-01-98' and '14-03-01' not the exact year . Could you please help me to get correct date format.
Regards,
Uday