I am trying to assign the result of a dateadd expression to a variable in a Snowflake sql script.
The assignment works fine when I use any date_or_time_part EXCEPT nanosecond. When I use nanosecond, I get the following error:
Unsupported feature 'assignment from non-constant source expression'**
Is there a reason nanosecond behaves differently, or is this a bug?
-- 'second' is successfully set into the variable
set foo = dateadd("second" , -1, '2019-10-25');
-- 'nanosecond' throws an error:
-- Unsupported feature 'assignment from non-constant source expression'.
set bar = dateadd("nanosecond", -1, '2019-10-25');
This is indeed a current limitation of Snowflake.
Today, in set expressions the right side is supposed to be a simple expression, for which there's a "fast" computation path (not to get too much into details). For "second", such a path exists, for "nanosecond", it doesn't.
You can work around this by making the right expression a "subquery" expression, like this:
set x = (select dateadd(nanosecond, -1, '2019-10-25'));