Solved

How do I reformat date when exporting data from Oracle?

Posted on 2011-03-01
7
650 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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ORACLE SQL DEVELOPER Query ? 5 60
oracle query 15 63
oracle query help 36 67
Deny Oracle DBAs from Connecting  "/ as sysdba" 5 40
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

758 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

21 Experts available now in Live!

Get 1:1 Help Now