Link to home
Start Free TrialLog in
Avatar of cassie5643
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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

You can use any format mask you want and present the date in any format.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.'MM/DD/YYYY HH24:MI:SS AM'),'MM/DD/YYYY HH24:MI:SS')||':;:'||
          to_char(to_date(columnE.'MM/DD/YYYY HH24:MI:SS AM'),'MM/DD/YYYY HH24:MI:SS')||':;:'||
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
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.'MM/DD/YYYY HH24:MI:SS AM'),'YYYY-MM-DD HH24:MI:SS')||':;:'||
          to_char(to_date(columnE.'MM/DD/YYYY HH24:MI:SS AM'),'YYYY-MM-DD HH24:MI:SS')||':;:'||
from db_name.table_xyz;
spool off