| decode in control file [message #372220] |
Fri, 19 January 2001 08:55 |
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),'000000',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
and I have to <= in decode is this possible
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
