CODEWITHSUNDEEP
mysqlsqldatehananvarchar
Diana Vazquez Romo
Diana Vazquez Romo

Reputation: 212

SQL Query nvarchar to date

I am working on SAP HANA Studio and have tried to run SQL command that converts an entire column of field, nvarchar, into one of field, date.

My dates have format: dd-mon-yyyy (i.e '29-Mar-1997') with field nvarchar(11).

I have looked at previous questions and SQL command documentation (for functions like CAST, CONVERT, TO_DATE, STR_TO_DATE) and have not gotten a solution.

Typical errors I get are: Function not recognized, or, Error while parsing Service Date as DATE at function to_date().

Any suggestions?

Thanks -Diana

Upvotes: 0

Views: 656

Answers (2)

ESP32
ESP32

Reputation: 8728

Obviously your database driver/layer in SAP HANA does not support all mySQL functions. Please connect to your database directly (using command-line or a gui like HeidiSQL) and create a view in your database:

CREATE VIEW view_tablename AS
SELECT STR_TO_DATE(`Service Date`, '%d-%b-%Y') AS ServiceDateDt, * FROM tablename

Then use view_tablename instead of tablename in all your queries - because view_tablename has the additional date field "ServiceDateDt".

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Try TO_DATE():

select to_date(col, 'DD-MON-YYYY')

Upvotes: 2

Related Questions