cassie5643
asked on
How do I reformat date when exporting data from Oracle?
Hello,
I have written a script to export data from an Oracle database via command line but need to reformat the date/timestamp so I can import it into MySQL tables. Would appreciate suggestions... I have pasted my script below. columnD and columnE are seperated by a delimiter :;: and need to be altered from this format (mm/dd/yyyy hh:mm:ss AM/PM) to this format (yyyy-mm-dd hh:mm:ss) Thank you.
--TABLE_XYZ
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on heading off verify off feedback off linesize 500
spool /export/table_xyz.txt
select columnA ||':;:'|| columnB ||':;:'|| columnC ||':;:'|| columnD ||':;:'|| columnE
from db_name.table_xyz;
spool off
I have written a script to export data from an Oracle database via command line but need to reformat the date/timestamp so I can import it into MySQL tables. Would appreciate suggestions... I have pasted my script below. columnD and columnE are seperated by a delimiter :;: and need to be altered from this format (mm/dd/yyyy hh:mm:ss AM/PM) to this format (yyyy-mm-dd hh:mm:ss) Thank you.
--TABLE_XYZ
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on heading off verify off feedback off linesize 500
spool /export/table_xyz.txt
select columnA ||':;:'|| columnB ||':;:'|| columnC ||':;:'|| columnD ||':;:'|| columnE
from db_name.table_xyz;
spool off
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use any format mask you want and present the date in any format.
Here's a decent reference:
http://psoug.org/reference/date_func.html
http://psoug.org/reference/date_func.html
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What type of data is stored in columnD and columnE? Are they DATE or VARCHAR2 columns?
If DATE, then slightwv's solution will work for you, otherwise, if you are storing the dates in character columns, you will need to first convert them to date, before converting to the required format.
e.g.
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on heading off verify off feedback off linesize 500
spool /export/table_xyz.txt
select columnA ||':;:'|| columnB ||':;:'|| columnC ||':;:'||
to_char(to_date(columnD.'M M/DD/YYYY HH24:MI:SS AM'),'MM/DD/YYYY HH24:MI:SS')||':;:'||
to_char(to_date(columnE.'M M/DD/YYYY HH24:MI:SS AM'),'MM/DD/YYYY HH24:MI:SS')||':;:'||
from db_name.table_xyz;
spool off
If DATE, then slightwv's solution will work for you, otherwise, if you are storing the dates in character columns, you will need to first convert them to date, before converting to the required format.
e.g.
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on heading off verify off feedback off linesize 500
spool /export/table_xyz.txt
select columnA ||':;:'|| columnB ||':;:'|| columnC ||':;:'||
to_char(to_date(columnD.'M
to_char(to_date(columnE.'M
from db_name.table_xyz;
spool off
Milleniumaire, doesn't that simply convert the string back to itself?
To convert to the new format the double conversion needs the second format as shown in my previous post
To convert to the new format the double conversion needs the second format as shown in my previous post
Yes, sorry, I copied the wrong format due to getting sidetracked ;-) It's a little late now, but it should be:
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on heading off verify off feedback off linesize 500
spool /export/table_xyz.txt
select columnA ||':;:'|| columnB ||':;:'|| columnC ||':;:'||
to_char(to_date(columnD.'M M/DD/YYYY HH24:MI:SS AM'),'YYYY-MM-DD HH24:MI:SS')||':;:'||
to_char(to_date(columnE.'M M/DD/YYYY HH24:MI:SS AM'),'YYYY-MM-DD HH24:MI:SS')||':;:'||
from db_name.table_xyz;
spool off
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on heading off verify off feedback off linesize 500
spool /export/table_xyz.txt
select columnA ||':;:'|| columnB ||':;:'|| columnC ||':;:'||
to_char(to_date(columnD.'M
to_char(to_date(columnE.'M
from db_name.table_xyz;
spool off