CODEWITHSUNDEEP
exceldate-formathana
Sabomnim
Sabomnim

Reputation: 1

HANA SQL:Import Excel Field formatted as date into Dataset or convert excel date values (eg. 43830 for 31.12.2019) to Date

I'd like to upload from an Excel file with a column formatted as date in excel to a dataset in the Analytical workspace of Tagetik on HANA database. As excel stores date values as numbers (eg43830 foe 31.12.2019) I get an orror uploading in a dataset field with type date as 43830 is not a valid date format. Uploading the excel field in a Dataset field with type text or number works, but then I have the numeric representation there.

So I'd like to convert this number (43830) to a valid date (31.12.2019) - I did not find an appropirate function in HANA SQL.

Thanks for some ideas.

best

Sabom

Upvotes: 0

Views: 361

Answers (1)

breadcrumb42
breadcrumb42

Reputation: 225

Why do you dont convert the number to a date as string in excel before upload and just use that date? Sounds like the easiest. Nontheless, you could use ADD_DAYS in HANA. The number you are seeing are days since January, 1st 1900. So ADD_DAYS with a base of 1900-01-01 should work.

Upvotes: 1

Related Questions