The date functions in snowflake doesn't seem to work as expected

Here is the doc for the week of the year policy that decides how this works - https://docs.snowflake.net/manuals/sql-reference/parameters.html#week-of-year-policy

An example might help. week_of_year_policy is set to 0 by default on newly created accounts.

select YEAROFWEEK('2017-01-01T00:00:00.000+00:00'::timestamp) as yow
     , WEEK('2017-01-01T00:00:00.000+00:00'::timestamp) as w;

+------+----+
|  YOW |  W |
+------+----+
| 2016 | 52 |
+------+----+

alter session set week_of_year_policy = 1;

select YEAROFWEEK('2017-01-01T00:00:00.000+00:00'::timestamp) as yow
     , WEEK('2017-01-01T00:00:00.000+00:00'::timestamp) as w;

+------+---+
|  YOW | W |
+------+---+
| 2017 | 1 |
+------+---+