BigQuery从日期字段提取星期作为日期范围

根据所需的输出、单独的列或单个字符串,您可以尝试以下操作:

with sample_data as (
    SELECT date FROM UNNEST(generate_timestamp_array('2021-12-01 00:00:00', '2022-02-01 00:00:00', INTERVAL 1 DAY)) as date
)

select
    date
    , DATE_TRUNC(DATE(date), WEEK) week_start
    , DATE_TRUNC(DATE(date), WEEK) +6 week_end
    , LAST_DAY(DATE(date), WEEK) as alt_week_end
    , concat(DATE_TRUNC(DATE(date), WEEK)," - ", DATE_TRUNC(DATE(date), WEEK) +6) week_range
from sample_data 

考虑下面的方法

select *, 
  date_trunc(date(date), week) week_start,
  last_day(date(date), week) week_end
from your_table    

输出类似