Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Insert script for excel with different time format

Uday_NFeb 18 2022

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

This post has been answered by Frank Kulash on Feb 18 2022
Jump to Answer

Comments

Post Details

Added on Feb 18 2022
10 comments
1,478 views