How to convert date field from .csv file to SAP HANA TIMESTAMP

[Former Member]

Hi,

I am new to hana and would like some help. I imported flat file (.csv) into HANA. The date format is as below;

SQL DATA TYPE            COLUMN STORE DATA TYPE

NVARCHAR                    STRING

I want to convert the existing time date format from 3/13/2016 -> TIMESTAMP 2016-03-13 11:09:00.0 (YYYY-MM-DD HH24:MI:SS).

I tried to change the data type to TIMESTAMP during the import process of the flat file but it throws an error, shown in attached.

Any help is much appreciated.

[joseph_reddy88 · Accepted solution]

Hi Saif,

Can you check your flat file date format in '2011-05-11 12:00:00' . If so it will be loaded to Time stamp data type.

Else can you convert the varchar to TIMESTAMP, try Using below in sql console

SELECT (TO_TIMESTAMP("Hire Start Date",'YYYY-MM-DD HH:SS.FF3')) as NEW DATE FROM TABLE.

Thanks,

Joseph

[Former Member · Accepted solution]

Hi Joseph,

Thanks for your response. I tried to change NVARCHAR to TIMESTAMP using sql below

SELECT (TO_TIMESTAMP("Hire Start Date",'YYYY-MM-DD HH:SS.FF3')) as NEW DATE FROM TABLE.

It gives me a sql syntax error: incorrect syntax near "DATE": line 1 col 72 (at pos 72)

[joseph_reddy88 · Accepted solution]

Hi Saif,

You can do other Work Around to Load Date fields from excel to HANA. In HANA side, keep the corresponding column as date data type and change the date format in excel YYYY-MM-DD (Using format custom option).

Now try loading the table to HANA using Data from Local file option  and it will be successful.This will work only in cases, if you dont have a null value under the date column.

Thanks,

Joseph