Know the week number of a month in bigquery

Question

I want to know the week number of a month for a date in bigquery standard sql.In PostgreSQL if I write:

select To_char(current_date, 'YYYY-MM-W')<br>

It works for the date '25-04-2018' as 2018-04-4. Here 2018 is the year, 04 is the month and 4 is the fourth week of the month in which the date falls.

I want something similar in bigquery standard sql. If I write:

select format_date("%Y-%m",current_date())

It gives only 2018-04

I also want to know the week number of month. Thank you in advance.

If the second day of a month falls on a Sunday, which week number is it? It's not clear how you expect to count weeks, e.g. if the first of a month through the seventh is week 1, or whether it's determined somehow by a week boundary. — Elliott Brossard, Apr 25 '18 at 09:22
If second day of a month falls on Sunday it still falls in week 1. 1 st to 7th come under week 1,8 to 14 under week 2 and so on. — Sweta, Apr 25 '18 at 09:31

score 1 · Accepted Answer · answered Apr 25 '18 at 09:50

Here is solution (defining a UDF that you can use in a query) along with an example.

CREATE TEMP FUNCTION DateWithWeekOfMonth(date DATE) AS (
  CONCAT(
    FORMAT_DATE('%Y-%m-', date),
    CAST(DIV(EXTRACT(DAY FROM date), 7) + 1 AS STRING)
  )
);

SELECT date, DateWithWeekOfMonth(date)
FROM (
  SELECT DATE '2018-04-01' AS date UNION ALL
  SELECT DATE '2018-04-07' UNION ALL
  SELECT DATE '2018-04-08' UNION ALL
  SELECT DATE '2018-04-30'
);

Know the week number of a month in bigquery

1 Answers1