CODEWITHSUNDEEP
hanasap-business-technology-platform
Ambika ..
Ambika ..

Reputation: 21

SQL-How to Convert string to timestamp (DD-MM-YYYY HH:MM:SS to timestamp)

I am using SAP HANA BTP-SQL.

I have a order table with a column CREATED_DATETIME . Datatype is varchar (date format is not standard-so datatype fixed as string). Would like to convert it to timestamp in the format 'YYYY-MM-DD HH:MM:SS'. Since I am beginner , please help with script to convert from string to timestamp. Tried below scripts- getting error-please help me to convert to timestamp:

created_datetime           Expected timestamp
01-09-2022  11:50:34       2022-09-01 00:00:00

This order table,column created_datetime value has 01-09-2022 11:50:34.Still unable to convert.

I have to convert all the records from created_datetime column to timestamp in the format 2022-09-01 00:00:00

Tried below scripts- getting error-please help me to convert to timestamp:

SELECT TO_TIMESTAMP (CREATED_DATETIME,'YYYY-MM-DD HH:MM:SS') FROM ORDER_TABLE;

error: (dberror) [303]: invalid DATE, TIME or TIMESTAMP value: SQL Error

SELECT TO_DATE (CREATED_DATETIME,'YYYY-MM-DD HH:MM:SS') from ORDER_TABLE;

error: (dberror) [303]: invalid DATE, TIME or TIMESTAMP value: SQL Error

SELECT STR_TO_DATE (CREATED_DATETIME,'%Y-%m-%d %H:%i:%s') from ORDER_TABLE;

(dberror) [328]: invalid name of function or procedure: STR_TO_DATE

SELECT UNIX_TIMESTAMP(STR_TO_DATE(CREATED_DATETIME, '%Y-%m-%d %h:%i:%s')) from ORDER_TABLE;

(dberror) [328]: invalid name of function or procedure: UNIX_TIMESTAMP

Upvotes: -1

Views: 3169

Answers (1)

nbk
nbk

Reputation: 49373

if your date is 01-09-2022 11:50:34

you need the same format as the original data

SELECT TO_TIMESTAMP (CREATED_DATETIME,'DD-MM-YYYY H24:MI:SS') FROM ORDER_TABLE;

Upvotes: 0

Related Questions