During an Oracle to Snowflake migration project, we ran into the following code to migrate:

to_char(datecolumn, 'YYYYWW')

Snowflake has an extensive date and time function library, so I expected it to work out of the box. To my surprise, this was not the case. The 'WW' (week of year) syntax is (at the time of writing this article) not supported by Snowflake. A full list of supported formats can be found here: https://docs.snowflake.com/en/sql-reference/functions-date-time

The Oracle documentation states that the WW format works like this:

• The first 7 days of the year are always considered as week 1.
• From the 8th day onward, the 'normal' way of counting weeks starts again.

This can lead to non-intuitive results, such as week 2 consisting of only a few days. As such, I would always recommend using the standard isoweek format instead.

To reproduce the Oracle syntax I decided to calculate the week myself:

FLOOR((DAYOFYEAR(datecolumn)+6)/7)

This will reproduce the week numbers exactly as in Oracle but does not include the leading zero. In our case we do want that leading zero so let's elaborate on the code.

LPAD(FLOOR((DAYOFYEAR(datecolumn)+6)/7), 2, 0)

That's more like it. Now let's take a look at the final result.

Oracle:

SELECT generated_date
	, to_char(generated_date, 'ww') AS current_date_ww
	, to_char(generated_date, 'yyyyww') AS current_date_yyyyww
FROM (
	select to_date('20230101', 'YYYYMMDD') + LEVEL -1 AS generated_date
	from dual
	connect by level <= 14
) generated_dates;

Snowflake:

SELECT generated_date
	, LPAD(FLOOR((DAYOFYEAR(generated_date)+6)/7), 2, 0) AS current_date_ww
	, YEAR(generated_date)||LPAD(FLOOR((DAYOFYEAR(generated_date)+6)/7), 2, 0) AS current_date_yyyyww
FROM (
	SELECT to_date('20230101', 'YYYYMMDD') + seq4() AS generated_date
	FROM TABLE(GENERATOR(ROWCOUNT => 14)) v 
) generated_dates;

Side note: Even though there can be a valid use case for the WW format, in general I would always advise making use of the ISO formats where possible.