January 1st = Week 1

The below gives me week numbers where week 1 starts on 1/4/2021 date_trunc('week', transaction_date) as week_number How can I create a week_number where the week starts on J

Solution 1:

A simple way is to use date arithmetic:

select1 + (transaction_date - date_trunc('year', transaction_date)) / 7as year_week

Solution 2:

The below gives me week numbers where week 1 starts on 1/4/2021

It is the default behaviour and it is defined that way in ISO.

WEEK_OF_YEAR_POLICY

Type Session — Can be set for Account » User » Session

Description

Specifies how the weeks in a given year are computed. Values

0: The semantics used are equivalent to the ISO semantics, in which a week belongs to a given year if at least 4 days of that week are in that year.

1: January 1 is included in the first week of the year and December 31 is included in the last week of the year.

Default 0 (i.e. ISO-like behavior)

It could be overrriden on multiple levels. The most granular is on the session level:

ALTER SESSION SET WEEK_OF_YEAR_POLICY =1;

Then you could use the standard code:

SELECT date_trunc('week', transaction_date)     as week_number
FROM ...;