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
Solved

How do I reformat date when exporting data from Oracle?

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

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
SQL Developer 6 60
Oracle - SQL Query with Function 3 53
having some issue on pl sql procedure 1 23
return value in based on value passed 6 27
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

856 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