Thanks for the question, Prajith.
Asked: July 25, 2006 - 12:45 am UTC
Last updated: July 25, 2006 - 12:50 pm UTC
Version: 10.1.0
Viewed 1000+ times
You Asked
Actually I'm receving a flat file having a date format as CCYYMMDD
so if data is coming as 21060423 I should read it as 23/04/2006 where as if I'm receving it as 20450423 I should read it as 23/04/1945.
how can I accomplish this.Is there any date format for this .
I have solved this as below
SELECT TO_DATE( TO_CHAR(TO_DATE(l_buff_table (24),'RRYYMMDD'),'DD/MM/RRRR'),'DD/MM/RRRR')INTO l_cont_rec.cr_value_date FROM dual;
Now this will read as 21 century only..
Thanks in advance
and Tom said...
ops$tkyte%ORA10GR2> select x,
2 add_months(to_date(x,'yyyymmdd'),-100*12) d1,
3 to_date( to_char(to_number(substr(x,1,2))-1,'fm99') || substr(x,3), 'yyyymmdd') d2
4 from t;
X D1 D2
------------------------------ -------------------- --------------------
21060423 23-apr-2006 00:00:00 23-apr-2006 00:00:00
20450423 23-apr-1945 00:00:00 23-apr-1945 00:00:00
I believe the add_months will work with all leap years, that would have to be verified. the to_date(to_char.....) will work.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment