Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I reformat date when exporting data from Oracle?

Posted on 2011-03-01
7
Medium Priority
?
667 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 1000 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

715 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