I have requirement where I have to convert the date format from 'CCYYMMDD' to 'YYYYMMDD'. Eg: '21060716' to '20060716' or '20980515' to '19980515'.
Please provide some solution for this.
Note: Input is a .dat file and output is Oracle DB.
Thanks in advance.
Hi Patra ,
In a expression use the following code this will solve your problem
its bit complex but you can short the code by doing the ltrim rtim in a variable and use that varaible.
we can not directly use TO_DATE as INFA do not support CCYYMMDD foramat
let me know if you still face any problem
I have arequirement to convert date in the format ddmmyyyy to format ccyy-mm-dd and i tried to use your formular and played around withg it but it looks like i cant do it .Can you help me as well please.
While reading source data read it as YYYY-MM-DD
TO_INTEGER(SUBSTR(TO_CHAR(year,'YYYY'),1,2))-1 || TO_CHAR(year,'YYMMDD') Swetha
TO_INTEGER(SUBSTR(TO_CHAR(year,'YYYY'),1,2))-1 || TO_CHAR(year,'YYMMDD')
Assumption: INPUTDATE is in STRING formate
Definitely the best and quickest way to convert string formatted as CCYYMMDD into date is this above shown by Madhurima.
If you need output into format CCYY-MM-DD then you should use this (assumption ->DATE_FIELD is in DATE/TIME format: