How to create date based on year, week number and day in bigquery

1
votes

How to form date from the year, week number and day value in Bigquery query.

For example- Year =2019, week number =37 day=Wednesday.

Output: (2019-09-18)

Year =2019, week number =36 day=Tuesday.

Output: (2019-09-10)

Here month will be calculated from the week and date will be based on when the given day falls in that week.

Note: Bigquery starts a week from 0, so if check above value for comparison in another system it could be 38 and 37

2
google-bigquery

2 Answers

1
votes

Below is for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 2019 year, 37 week, 'Wednesday' day UNION ALL
  SELECT 2019, 36, 'Tuesday' 
)
SELECT *, 
  (
    SELECT DATE_ADD(DATE_ADD(DATE(year, 1, 1), INTERVAL week WEEK), INTERVAL hit DAY)         
    FROM UNNEST(GENERATE_ARRAY(0, 6)) hit
    WHERE FORMAT_DATE('%A', DATE_ADD(DATE_ADD(DATE(year, 1, 1), INTERVAL week WEEK), INTERVAL hit DAY)) = day
  ) AS result
FROM `project.dataset.table`

with result as 

Row year    week    day         result   
1   2019    37      Wednesday   2019-09-18   
2   2019    36      Tuesday     2019-09-10
1
votes

Shouldn't it be week-1 like this:

WITH tbl AS (
  SELECT 2020 year, 21 week, 'Friday' day
)
SELECT *, 
  (
    SELECT DATE_ADD(DATE_ADD(DATE(year, 1, 1), INTERVAL week-1 WEEK), INTERVAL hit DAY)         
    FROM UNNEST(GENERATE_ARRAY(0, 6)) hit
    WHERE FORMAT_DATE('%A', DATE_ADD(DATE_ADD(DATE(year, 1, 1), INTERVAL week-1 WEEK), INTERVAL hit DAY)) = day
  ) AS result
FROM tbl

Otherwise it returns the date of the next week