how can i find the number of business days between two of my date columns in bigquery?

Question

this may seem simple but i can't find a solution to this online (without using functions).. i am using bigquery and want to find the number of business days between two days .

df['business_days'] = np.busday_count(date1,
                                     date2)
df['number_weekenddays'] = np.busday_count(date1,
                                      date2,
                                      weekmask='Sat Sun')

i am trying to replicate the above query in python into bigquery syntax. how can i do this? i have tried:

select
  order_date,
  pickup_date,
  case 
    when date_diff(pickup_date, order_date, week) > 0 
      then date_diff(pickup_date, order_date, day) - (date_diff(pickup_date, order_date, week) * 2)
    else
      date_diff(pickup_date, order_date, day) 
  end
from `orders.table` 

But I get the following error:

No matching signature for function DATE_DIFF for argument types: DATETIME, DATETIME, DATE_TIME_PART. Supported signature: DATE_DIFF(DATE, DATE, DATE_TIME_PART) at [186:10] Learn More about BigQuery SQL Functions.

even though the date column in question is a date

Answer 1

Probably the simplest approach is to enumerate the days between the two dates, and filter out non-business days. generate_date_array() comes habdy for this:

select o.order_date, o.pickup_date, 
    (
        select count(*)
        from unnest(generate_date_array(date(o.order_date), date(o.pickup_date))) dt
        where dateofweek(dt) between 2 and 6
    ) cnt_business_days
from `orders.table` o