CODEWITHSUNDEEP
sqlgoogle-bigquery
Jesus Navarro
Jesus Navarro

Reputation: 65

EXTRACT QUARTER FROM DATE IN BIGQUERY

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

Upvotes: 0

Views: 14294

Answers (3)

mjruttenberg
mjruttenberg

Reputation: 218

A string of date '2022-09-26' can be treated as a date using DATE():

SELECT DATE('2022-09-26');

This should return a date value instead of a string.

Another way to do this if your dates are not in YYYY-MM-DD format is to use PARSE_DATE('%Y%m%d','<some date value>') e.g. PARSE_DATE('%Y%m%d','20220926') returns a date value of 2022-09-26.

Let's check that DATE() works using your table and field: SELECT DATE(case_opening_date) FROM MY_TABLE LIMIT 5;

Now let's extract the quarter + "(" + year + ")":

SELECT *,
  CONCAT(
    EXTRACT(QUARTER FROM DATE(case_opening_date)), --convert the date and extract the quarter
    '(',
    EXTRACT(YEAR from DATE(case_opening_date)), -- convert the date and extract the year
    ')'
  ) AS quarteryear
FROM MY_TABLE;

An older way of getting the quarter is using EXTRACT(MONTH FROM field) and banding them into quarters:

SELECT *,
  CONCAT(
    --calculate quarter using month and banding them into 3 month blocks
    CASE
      WHEN EXTRACT(MONTH FROM DATE(case_opening_date)) BETWEEN 1  AND 3  THEN 1
      WHEN EXTRACT(MONTH FROM DATE(case_opening_date)) BETWEEN 4  AND 6  THEN 2
      WHEN EXTRACT(MONTH FROM DATE(case_opening_date)) BETWEEN 7  AND 9  THEN 3
      WHEN EXTRACT(MONTH FROM DATE(case_opening_date)) BETWEEN 10 AND 12 THEN 4
    END,
    '(',
    EXTRACT(YEAR from DATE(case_opening_date)), -- convert the date and extract the year
    ')'
  ) AS quarteryear
FROM MY_TABLE;

Some flavours of SQL (e.g. BigQuery) have another way to concatenate, using ||.

SELECT *,
  EXTRACT(QUARTER FROM DATE(case_opening_date))||
  '('||
  EXTRACT(YEAR from DATE(case_opening_date))||
  ')' AS quarteryear
FROM MY_TABLE;

Upvotes: 0

Alex Lopez
Alex Lopez

Reputation: 1

CASE WHEN extract(month from date) = 1 OR extract(month from date) = 2 OR extract(month from date) = 3 THEN CONCAT('Q4./',LEFT(CAST(EXTRACT(YEAR FROM DATE)-1 AS STRING),4)) WHEN extract(month from date) = 4 OR extract(month from date) = 5 OR extract(month from date) = 6 THEN CONCAT('Q1./',LEFT(CAST(DATE AS STRING),4)) WHEN extract(month from date) = 7 OR extract(month from date) = 8 OR extract(month from date) = 9 THEN CONCAT('Q2./',LEFT(CAST(DATE AS STRING),4)) WHEN extract(month from date) = 10 OR extract(month from date) = 11 OR extract(month from date) = 12 THEN CONCAT('Q3./',LEFT(CAST(DATE AS STRING),4)) END AS Quarter

OUTPUT: Q4./2022

Considering please the following date format 'YYYY-MM-DD'

Good Lucky! (^:

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173036

Use below

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

with output

enter image description here

Upvotes: 6

Related Questions