?
Solved

Oracle - generating date from (days from Jan 1) in SQL LOADER

Posted on 2013-06-21
7
Medium Priority
?
788 Views
Last Modified: 2013-06-22
We are using SQL Loader to take data from a text file into an Oracle table as we have done many times before.
The date format in the text file is 05150 - two digit year followed by the days since Jan 1 of that year.   We need to convert that into a standard date AND assume that anything < 60 is 1900 + two digit year.

Is there a way to do this in SQL LOADER?

Or, is there a way to call an external DLL or EXE from SQL Loader?
0
Comment
Question by:GNOVAK
[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

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 39266636
It can be done in sql loader.

Check out this sample control file:
LOAD DATA
CHARACTERSET WE8ISO8859P1
infile *
INTO TABLE tab1
truncate
(
col1 char(5) "to_date(case when to_number(substr(:col1,1,2)) < 60 then '19' else '20' end || :col1,'YYYYDDD')"
)
begindata
05150

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39266639
So you would expect 05150 to be 30-MAY-1905?
0
 
LVL 32

Accepted Solution

by:
awking00 earned 1000 total points
ID: 39266681
I suspect you mean the other way around so
05150 would be 30-MAY-2005 and
65150 would be 30-MAY-1965.
If you can live with Oracle's RR designation, which treats years less than 50 as being 1900 and those 50 and above as 2000, then you can just use -
LOAD DATA
INTO tablename
TRUNCATE
(
col1 "to_date(:col1,'RRDDD')"
)
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:GNOVAK
ID: 39267213
It would be the opposite of RR I believe.
If it's greater than 50 it would be 1900 and if it's less than 50 it would be 2000
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 39267298
Using the example I posted, it can be whatever you want.

>>If it's greater than 50 it would be 1900 and if it's less than 50 it would be 2000

I think awking00 mis-typed.

The docs explain how RR works:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements004.htm#i116004
0
 

Author Closing Comment

by:GNOVAK
ID: 39268044
Excellent - Thanks!
0
 
LVL 32

Expert Comment

by:awking00
ID: 39268184
Sorry for having mistyped, but you got it figured out :-)
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

771 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