[bigquery]対象日付を基準にして、特定曜日の日付を抽出する方法

2021-08-28 2022-02-25

-- 事前にDECLAREで変数定義
DECLARE target_date DATE;
-- 変数に値を設定
SET target_date = DATE('2021-01-01');

SELECT
  target_date
  , DATE_TRUNC(target_date, WEEK(MONDAY)) AS target_monday
  , DATE_TRUNC(target_date, WEEK(TUESDAY)) AS target_tuesday
  , DATE_TRUNC(target_date, WEEK(WEDNESDAY)) AS target_wednesday
  , DATE_TRUNC(target_date, WEEK(THURSDAY)) AS target_thursday
  , DATE_TRUNC(target_date, WEEK(FRIDAY)) AS target_friday
  , DATE_TRUNC(target_date, WEEK(SATURDAY)) AS target_saturday
  , DATE_TRUNC(target_date, WEEK(SUNDAY)) AS target_sunday

◆実行結果