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.

Answer 1

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'
);