Solved

How do I reformat date when exporting data from Oracle?

Posted on 2011-03-01
7
651 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 76

Accepted Solution

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

Expert Comment

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

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35008372
Here's a decent reference:
http://psoug.org/reference/date_func.html
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Finace 3 66
select query - oracle 16 92
FRM-40735:KEY-COMMIT trigger raised unhandled exception ORA-01422 7 52
data lookup in Oracle - need suggestions 55 102
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

919 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now