Page 1 of 1
Date Conversion problem
Posted: Fri May 16, 2008 5:59 am
by seshikumar
Hi,
i have requirement like this..........
my source data coming from COBOL DATE format (0823 MMYY) so i want to load the date into Oracle date format like(08/23 MM/YY) ,i have tried Ocon and Iconv but its not working...........
plz do needful................
Posted: Fri May 16, 2008 6:23 am
by ArndW
Is "0823" supposed to be "August, 2023" or "August, 1923" or another date? In order to put this into an Oracle DATE you will need to add a day-of-month as well. What should that be, day 1?
Re: Date Conversion problem
Posted: Fri May 16, 2008 6:26 am
by sachin1
Just convert your input 0823 into 08/23 like "MM/YY" ........Then use
OCONV(ICONV("05/08","D2/MY"),"D/E") to give you date in your format....check for oconv for various formats.......using proper format you can insert it into oracle database.
OCONV(ICONV("05/08","D2/MY"),"D/E") -----> 01/05/2008
Posted: Fri May 16, 2008 6:27 am
by seshikumar
yes your cooerct Arndw date is("August, 2023") so dont want to day.
Posted: Fri May 16, 2008 6:31 am
by chulett
Then you cannot store it in an Oracle DATE as they require a full date. If you literally just want to store 'MMYY' you'd need a CHAR or VARCHAR2 field for that, otherwise you will need to add a 'day' to the date before formatting it for Oracle.
After that, people can choose not to extract the day when selecting the field.
Posted: Fri May 16, 2008 6:36 am
by ArndW
So one possible answer is
Code: Select all
ICONV('01':In.CobDateCol,'D2DMY[2,2,2]')
to get an internal date by adding day 1 of each month.
Posted: Fri May 16, 2008 6:40 am
by seshikumar
hi Sachin1,
i have tried like this but i am not geting...........
Oconv(Iconv(DSLink22.ATS270_CARD_EXPIRY,"D2 MY"),"D-DMY[2,A3,4]")
plz help ...........
Posted: Fri May 16, 2008 6:41 am
by seshikumar
hi Sachin1,
i have tried like this but i am not geting...........
Oconv(Iconv(DSLink22.ATS270_CARD_EXPIRY,"D2 MY"),"D-DMY[2,A3,4]")
plz help ...........
Posted: Fri May 16, 2008 7:06 am
by sachin1
try below
Oconv(Iconv(DSLink22.ATS270_CARD_EXPIRY[1,2]:'/':DSLink22.ATS270_CARD_EXPIRY[3,4],"D2/ MY"),"D-DMY[2,A3,4]") it will give output like "01-MAY-2008", as date is not specified in your requirement it will always be 1st date of month.
Posted: Sun May 18, 2008 11:58 pm
by seshikumar
Hi Sachin1,
thanks for your help.
its working fine.
Posted: Mon May 19, 2008 2:53 am
by sachin1
Please can you mark the topic as resolved.