KNOWLEDGE BASE

Minutes Calculated Incorrectly When Converting DateTime to String in Snowflake


Published: 16 Dec 2018
Last Modified Date: 14 Aug 2019

Issue

When connected to Snowflake, converting datetime to string does not produce the correct results. 
For example, when converting datetime 2018-12-25 23:01:37 to string, the output is '2018-12-25 23:12:37' instead of '2018-12-25 23:01:37'.

Environment

  • Tableau Desktop
  • Snowflake

Resolution

Option 1: 

Contact Snowflake to get the fix to this issue. For more information, see [BUG] to_timestamp_ntz() gives different results for numeric vs variant-numeric at Snowflake support.

Option 2: 

As a workaround, manually convert the datetime using a calculation similar to the one below (replace [DATE] with your own date dimension):

STR(DATE([DATE] )) + " " +
IIF(DATEPART('hour',[DATE] ) < 10, "0" +STR(DATEPART('hour',[DATE] )), STR(DATEPART('hour',[DATE] ))) + ":" +
IIF(DATEPART('minute',[DATE] ) < 10, "0" +STR(DATEPART('minute',[DATE] )), STR(DATEPART('minute',[DATE] ))) + ":" +
IIF(DATEPART('second',[DATE] ) < 10, "0" +STR(DATEPART('second',[DATE] )), STR(DATEPART('second',[DATE] )))

Cause

This behavior is related to a known Snowflake issue. 
The issue is caused by how dates and time are rendered:  
  • Tableau format used to convert the datetime to string: 'YYYY-MM-DD HH:MM:SS'.
  • Snowflake format used to convert the datetime to string: 'YYYY-MM-DD HH:MI:SS' 

Additional Information







 
Did this article resolve the issue?