Converting Varchar to Timestamp in Snowflake

user_16629263 0
Viewed 321

I have a date defined as varchar. The value of the date field is 19810801. When I am trying to convert into Timestamp using To_TimeSTAMP the value returned is 1970-08-20 06:18:21.000 +0000. I am not sure why snowflake is setting the year as 1970 and month as August. Do you know what could be the issue here?

Here is my code - Select to_timestamp_tz(EFFECTIVE_DATE) from Table1

The output returned is 1970-08-20 06:18:21.000 +0000 for the EffectiveDate 19810801

snowflake-cloud-data-platform
1 Answers

TO_TIMESTAMP is evaluating the value as epoch time:

https://www.epochconverter.com/

If you want to convert it as 1981-08-01, you can use this:

Select to_timestamp_tz('19810801','YYYYMMDD');
user_125509650
Related