| decode in control file [message #372219] |
Fri, 19 January 2001 08:51  |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hi
Can somebody help me fixing the decode problem, I am some way
thru it , but not complete answer,
My situation is that I have to decide a date_of_birth
(input as mm/dd/yy) to turn mm/dd/yyyy basing on
the year
I built a query picking the year(2) and comparing
the same sysdate(year)last two digits so that they
are equal and calling it current century(20) else
calling it previous century, this works fine
another few years down the road but fails may be
in year 2020.
I am posting my logic, and can some body with
real fix. thanks in advance
---------------------
SELECT decode(substr(date_of_birth,1,6),'00000',NULL,
decode(to_number(substr(date_of_birth,5,2)),
to_number(substr(to_char(sysdate,'YYYY'),3,2)),
lpad(substr(date_of_birth,1,4),4,'0') ||to_number(substr(to_char(sysdate,'YYYY'),1,2))||substr(date_of_birth,5,2),
lpad(substr(date_of_birth,1,4),4,'0')||to_number(substr(to_char(sysdate,'YYYY'),1,2)-1)||substr(date_of_birth,5,2))) date_of_birth
FROM new_employee_v
--------------
ps: I have to fix it in sqlloader control file
where token length should not be more than 258 chars
|
|
|
|
| Re: decode in control file [message #372228 is a reply to message #372219] |
Fri, 19 January 2001 14:17  |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
There is another way, don't know you are aware of it. Just in case...
Oracle its self has date windowing 'RR'
if you insert a two digit year which is grater than 50 it will be consider as 1950, and if it is less that 50, it will take it as 2050.
If it is acceptable for your system you can use it
in the ctl file
simply
date_of_birth "to_date(:data_of_birth, 'MM/DD/RR')"
Bala....
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
