CODEWITHSUNDEEP
google-bigquery
rahulb
rahulb

Reputation: 63

Bigquery Custom first day of week (defaults Monday)

I am trying to extract week from date using the function

format_timestamp('%V', DATE)

but Bigquery documentation says "Monday as the first day of the week".

Is there a way where I can specify Tuesday as first day of week.

I have achieved the same in Redshift using

date_part(week, date + interval '-1 day')

Is it possible in Bigquery?

Upvotes: 4

Views: 8350

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Below is for BigQuery Standard SQL

You can use WEEK(WEEKDAY) as shown below 

#standardSQL
SELECT FORMAT_TIMESTAMP('%V', CURRENT_TIMESTAMP()), 
EXTRACT(WEEK(SATURDAY) FROM CURRENT_TIMESTAMP())

This was introduced quite recently - https://cloud.google.com/bigquery/docs/release-notes#november_30_2017

Upvotes: 6

Related Questions