Date Conversion problem

[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................

[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?

[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

[seshikumar]

yes your cooerct Arndw date is("August, 2023") so dont want to day.

[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.

[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.

[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 ...........

[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 ...........

[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.

[seshikumar]

Hi Sachin1,

thanks for your help.

its working fine.

[sachin1]

Please can you mark the topic as resolved.