Week to date (WTD) works like the other period-to-date totals: everything from the first day of the week up to a reference date. The part that needs care is the first day of the week, because in Snowflake each session can choose it.

A small example

Six orders around the week that starts on Monday 11 May 2026. The reference date is Friday 15 May.

CREATE TEMP TABLE Sales (OrderDate DATE, Amount NUMBER(10,2));
INSERT INTO Sales VALUES
  ('2026-05-09', 70),   -- Saturday
  ('2026-05-10', 40),   -- Sunday
  ('2026-05-11', 90),   -- Monday
  ('2026-05-13', 60),   -- Wednesday
  ('2026-05-15', 50),   -- Friday
  ('2026-05-16', 80);   -- Saturday

The query

WITH Params AS (
  SELECT AsOf,
         DATEADD(day, 1 - DAYOFWEEKISO(AsOf), AsOf) AS WeekStart
  FROM (SELECT '2026-05-15'::DATE AS AsOf)   -- CURRENT_DATE() in a live report
)
SELECT p.WeekStart, p.AsOf,
       COALESCE(SUM(s.Amount), 0) AS WTD
FROM Params p
LEFT JOIN Sales s
  ON  s.OrderDate >= p.WeekStart
  AND s.OrderDate <  DATEADD(day, 1, p.AsOf)
GROUP BY p.WeekStart, p.AsOf;
WeekStart AsOf WTD
2026-05-11 2026-05-15 200.00

DAYOFWEEKISO numbers the days from 1 (Monday) to 7 (Sunday). Going back that number minus one lands on Monday: Friday is 5, so the week started four days earlier, on 11 May. The orders of Monday, Wednesday and Friday add up to 200. The weekend before and the Saturday after are outside the range. As in how to calculate YTD, QTD and MTD, the range ends before the day after the reference date, so it also works on timestamps.

Why not DATE_TRUNC(‘week’)

DATE_TRUNC('week', AsOf) also returns Monday 11 May, but only while the WEEK_START parameter keeps its default value of 0. WEEK_START can be set for the account, for a user or for a single session, and with WEEK_START = 7 the same function returns Sunday 10 May. Two people running the same query can then see different totals.

DAYOFWEEKISO ignores WEEK_START, so the calculation above gives the same answer in every session.

Weeks that start on another day

For weeks that start on Sunday:

DATEADD(day, -MOD(DAYOFWEEKISO(AsOf), 7), AsOf) AS WeekStart

Sunday is 7, and MOD(7, 7) is 0, so a Sunday stays where it is; Monday is 1, so it moves back one day. With this start the example week begins on Sunday 10 May and WTD becomes 240, because it now includes the Sunday order of 40.

The general form, for a week that starts on ISO day N (1 for Monday up to 7 for Sunday), is DATEADD(day, -MOD(DAYOFWEEKISO(AsOf) - N + 7, 7), AsOf). For weeks that start on Saturday, N is 6:

DATEADD(day, -MOD(DAYOFWEEKISO(AsOf) + 1, 7), AsOf) AS WeekStart

Edge cases

References