Search code examples
timetype-conversionsnowflake-cloud-data-platform

How to convert varchar to time without changing values in Snowflake?

I need to convert a time value stored as varchar into "time" data value. But when I do the conversion it added 19 hours to my original value. I need as result the same value but converted to time.

SELECT '00:51:09' AS ORIGINAL_TIME_VARCHAR,
TO_TIME('00:51:09') AS CONVERT_TO_TIME;

the result in the time conversion looks: 19:51:09

Solution

  • It's due to the time zone. Converting time zones is not a straight forward process with time data.

    You can use this if you want a quick solution:

    SELECT '00:51:09' AS ORIGINAL_TIME_VARCHAR,
TO_TIME('00:51:09') - INTERVAL '19 HOURS' AS CONVERT_TO_TIME;