Solved Lecture d'une date à partir d'un fichier Excel

Hello Everyone,

I am using a Microsoft Excel Metadata to get dates from an Excel file in this format "DD/MM/YYYY".
The metadata return a code instead of the date.
Is there any way to read the value in the presented format?

Thank you

Hi,

Excel stores the dates as numbers.
The formatting that you can see in the Excel file is only for display purposes and everything is stored as number in the background.
It is so normal that the value returned by the driver is a number.

When using the Excel metadata in a mapping, you could use sql functions to convert data :
to_number(<column>)
to_date(<column>)
to_timestamp(<column>)
to_time(<column>)
...

Hi,
I've tried with to_date() function but it does not work for me.
for exemple to extract the day i tried: to_date(FIELD,'DD')
but it doesn't work

Hello Everyone,

Thanks for replying, it works for me now.
I've used the next formula:
to_date('1900-00-00','YYYY-MM-DD') + FIELD::integer
It works nice

Great, thanks for sharing your solution

Welcome

more precisely :

DATE('1900-01-01')+(INT(EXCELDATE)-2) days

cause Excel considers 1900/01/01 as 1-st day (not a 0 day) and that 1900 year is leap (but it is not)