How to convert Excel date

gsym · Post by **gsym** » Tue Aug 07, 2007 10:51 am

Hi All,
I have to populate an oracle table from an Excel sheet.
Converted the Excel sheet to a csv file, using sequential file stage to read it. Date field in the excel sheet in excel format ( like 38895) .
How to convert this to Standard Date Format ( YYYYMMDD) ?
Any help is appreciated..

chulett · Post by **chulett** » Tue Aug 07, 2007 10:59 am

Why not apply a Date format to the speadsheet column so you get a 'real' date in your saved csv rather than Excel's internal date number?

gsym · Post by **gsym** » Tue Aug 07, 2007 11:08 am

There is one more field with string and excel internal date like 'abc 38895'.
So i thought to do the conversion with the ETL.
I tried with Oconv(iconv(1900-01-01,"D-YMD[4,2,2]")+ 38895,"D-YMD[4,2,2])") ,but its not giving the correct date.

chulett · Post by **chulett** » Tue Aug 07, 2007 11:17 am

You'll need to figure out or look up what Excel uses for its 'zero date'. Shouldn't be too hard.