CODEWITHSUNDEEP
google-bigquery
Trishit Ghosh
Trishit Ghosh

Reputation: 255

Pass column value as Date Part argument

I am trying to generate a string array of weekdays and use it find how many times each day appears in a month

I am using standard sql on BigQuery

My query would look like 

with weeks as (select array['SUNDAY','MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY'] as wk)
select DATE_DIFF('2019-01-31','2019-01-01',WEEK(wk)) AS week_weekday_diff 
from weeks, unnest(wk) as wk

The query however fails with the error A valid date part argument for WEEK is required, but found wk. wk is a column value having the Days of Week, WEEK is a Functions which expects a literal DAYOFWEEK. Is there a way i pass the column value as arguments

Upvotes: 1

Views: 621

Answers (2)

bhito
bhito

Reputation: 2673

Trying your query, what I have noticed to be returning an error is:

select DATE_DIFF('2019-01-31','2019-01-01',WEEK('WEDNESDAY')) AS week_weekday_diff;

as the function WEEK(< WEEKDAY >) is expecting something like:

select DATE_DIFF('2019-01-31','2019-01-01',WEEK(`WEDNESDAY`)) AS week_weekday_diff;

OR

select DATE_DIFF('2019-01-31','2019-01-01',WEEK(WEDNESDAY)) AS week_weekday_diff;

I think that the WEEK(< WEEKDAY >) only accepts the weekdays in the format exposed here, so no strings should be valid.

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is for BigQuery Standard SQL

error "A valid date part argument for WEEK is required, but found wk" 

WEEK(<WEEKDAY>): Valid values for WEEKDAY are literal SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.

... Is there a way i pass the column value as arguments?

If you wish - you can submit feature request at https://issuetracker.google.com/issues/new?component=187149&template=0

find how many times each day appears in a month

To get your expected result and overcome above "issue" you can approach task from opposite angle - just extract weekdays positions and then do needed stats as in example below 

#standardSQL
WITH weekdays AS (SELECT ['SUNDAY','MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY'] AS wk)
SELECT wk[ORDINAL(pos)] weekday, COUNT(1) cnt
FROM weekdays, 
  UNNEST(GENERATE_DATE_ARRAY('2019-01-01','2019-01-31')) day, 
  UNNEST([EXTRACT(DAYOFWEEK FROM day)]) pos
GROUP BY pos, weekday
ORDER BY pos

with result 

Row weekday     cnt  
1   SUNDAY      4    
2   MONDAY      4    
3   TUESDAY     5    
4   WEDNESDAY   5    
5   THURSDAY    5    
6   FRIDAY      4    
7   SATURDAY    4

Upvotes: 1

Related Questions