Solved

How do I reformat date when exporting data from Oracle?

Posted on 2011-03-01
7
660 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:cassie5643
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 35008275
use to_char(date_col,'MM/DD/YYYY HH24:MI:SS')
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35008367
You can use any format mask you want and present the date in any format.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35008372
Here's a decent reference:
http://psoug.org/reference/date_func.html
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 35008374
if columns D and E are both strings in mm/dd/yyyy hh:mm:ss AM/PM  format then convert them to dates and reformat

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')


if those columns are dates, then they don't have formats and you can simply use to_char as shown above by slightwv but with the format you want

select columnA ||':;:'|| columnB ||':;:'|| columnC ||':;:'|| to_char(columnD,'yyyy-mm-dd hh24:mi:ss')  ||':;:'|| to_char(columnE,'yyyy-mm-dd hh24:mi:ss')
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 35008514
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35008619
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
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 35008680
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
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question