EXTRACT QUARTER FROM DATE IN BIGQUERY-CodePudding

i AM TRYING TO CREATE AN ADITIONAL COLUMN WITH A QUARTER VALUE FROM A DATE(case_opening_date FORMATTED AS 2022-09-26 which is a string and needs to be converted into a date), THE FORMAT for the new column SHOULD BE 1(2022) indicating the quarter from that date and in parenthesis the year from that quarter, TRIED THE CODE BELLOW BUT I RECEIVE AN ERROR: SQL_ANALYSIS_ERROR: No matching signature for function EXTRACT for argument types: DATE_TIME_PART FROM STRING. Supported signatures: EXTRACT(DATE_TIME_PART FROM DATE); EXTRACT(DATE_TIME_PART FROM TIMESTAMP [AT TIME ZONE STRING]); EXTRACT(DATE_TIME_PART FROM DATETIME); EXTRACT(DATE_TIME_PART FROM TIME)

WITH
  _0 AS (
    SELECT
      *,

      EXTRACT(QUARTER FROM case_opening_date) as Quarter
    FROM MY_TABLE AS _t
  )
SELECT * FROM _0

How can i convert case_opening_date into a date and get a new column with the quarter?

Thanks a lot in advance

CodePudding user response：

Use below

select case_opening_date,
  format_date('%Q(%Y)', date(case_opening_date)) as Quarter
from your_table

with output