string to timestamp with sql

Question

there is a column names request_time and its data type is string. the data is like 01-03-2024 16:58:44. i wanted to convert it to timestamp in hive. i have already tried a few things such as:

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(request_time, 'dd-MM-yyyy HH:mm:ss')) AS converted_timestamp FROM ...;
 
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(REGEXP_REPLACE(request_time, '(\\d{2})-(\\d{2})-(\\d{4}) (\\d{2}):(\\d{2}):(\\d{2})', '$3-$2-$1 $4:$5:$6'))) AS converted_timestamp FROM ...;
 
SELECT from_unixtime(unix_timestamp(request_time, 'dd-MM-yyyy HH:mm:ss'), 'dd-MM-yyyy HH:mm:ss') AS converted_timestamp FROM ...;
 
SELECT CAST(request_time AS TIMESTAMP) AS date_request from ...;

but none of the above worked. in some cases, the data still remained as a string, in other cases it became null.

Answer 1

Can you try this way

CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(request_time,'dd-MM-yyyy HH:mm:ss')) as TIMESTAMP) as converted_date