how to convert an entire column's datatype from number to timestamp in snowflake

Question

This statement gives me an error

      SELECT cast(FHEFTJ as Timestamp) 

however this works for

          SELECT cast('108353' as Timestamp) 

I wanted the entire columns datatype to timestamp so tried the above but it doesn't work

given entry 108353 wanted result as 1970-01-02 06:05:53.000

Answer 1

I assume you are trying to convert a table of values with FHEFTJ as the column name. You need to reference the table, though.

SELECT FHEFTJ::timestamp
FROM tablename;

or

SELECT to_timestamp(FHEFTJ)
FROM tablename;

or

SELECT CAST(FHEFTJ as timestamp)
FROM tablename;

Answer 2

I am assuming that FHEFTJ is one of the row value you have and its throwing error converting it to timestamp. try_to_numeric function first checks if its a number that can be converted.

SELECT CAST(try_to_NUMERIC(FHEFTJ) as Timestamp) FHEFTJ_datetimE FROM ( SELECT '103353' FHEFTJ union SELECT '108353' FHEFTJ union SELECT 'Somevalue' FHEFTJ ) ;