Most of time when Snowflake query complains about something “is not recognized”, it normally means that Snowflake is not able to CAST a given value to a target type. In my case of “Timestamp ‘2020-09-28’ is not recognized”, it means Snowflake is not able to convert value “2020-09-28” to Timestamp value.
This sounds a bit non-sense, as “2020-09-28” is a valid timestamp value, even without the time part. A quick test shows correct result:
SELECT TO_TIMESTAMP('2020-09-28');
+----------------------------+
| TO_TIMESTAMP('2020-09-28') |
|----------------------------|
| 2020-09-28 00:00:00.000 |
+----------------------------+
SELECT CAST('2020-09-28' AS TIMESTAMP);
+---------------------------------+
| CAST('2020-09-28' AS TIMESTAMP) |
|---------------------------------|
| 2020-09-28 00:00:00.000 |
+---------------------------------+
However, in Snowflake, when you perform TIMESTAMP related functions, there are two parameters that control how Snowflake READs (input) and WRITEs (output) timestamp values. Those parameters are:
The TIMESTAMP_OUTPUT_FORMAT controls the timestamp value returned back to the user in a result. I have discussed briefly in my previous post Round Timestamp Precision, so I will skip this part here.
The TIMESTAMP_INPUT_FORMAT controls how Snowflake reads the timestamp values passed into Snowflake’s functions, like CAST or TO_TIMESTAMP in my examples above.
To show the value of TIMESTAMP_INPUT_FORMAT, run below query:
SHOW PARAMETERS LIKE 'TIMESTAMP_INPUT_FORMAT'; +------------------------+-------+---------+-------+----------------------------+--------+ | key | value | default | level | description | type | |------------------------+-------+---------+-------+----------------------------+--------| | TIMESTAMP_INPUT_FORMAT | AUTO | AUTO | | input format for timestamp | STRING | +------------------------+-------+---------+-------+----------------------------+--------+
By default it is AUTO, so that Snowflake will detect automatically, hence my above examples work. However, if user overrides this setting at session level, or account level, then the user will have to follow the defined format.
Let’s change the value to something else and test again:
ALTER SESSION SET TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF3'; +----------------------------------+ | status | |----------------------------------| | Statement executed successfully. | +----------------------------------+
Then run the above two queries again, we can see that they will fail:
SELECT CAST('2020-09-28' AS TIMESTAMP);
100035 (22007): Timestamp '2020-09-28' is not recognized
SELECT TO_TIMESTAMP('2020-09-28');
100035 (22007): Timestamp '2020-09-28' is not recognized
Now let’s change again to the correct format, then both of them will work again:
ALTER SESSION SET TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD';
+----------------------------------+
| status |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
SELECT CAST('2020-09-28' AS TIMESTAMP);
+---------------------------------+
| CAST('2020-09-28' AS TIMESTAMP) |
|---------------------------------|
| 2020-09-28 00:00:00.000 |
+---------------------------------+
SELECT TO_TIMESTAMP('2020-09-28');
+----------------------------+
| TO_TIMESTAMP('2020-09-28') |
|----------------------------|
| 2020-09-28 00:00:00.000 |
+----------------------------+
So ideally leave it as default value of “AUTO” is the best option, as it will avoid unexpected query failure. However, if your organization need to follow strict rules about what data comes through, then you can update this setting, but make sure to educate your users, otherwise it will be very easy to cause confusion as the error message is not as obvious on what’s happening behind the scene.
Hope above information can be helpful.
