CODEWITHSUNDEEP
snowflake-cloud-data-platform
Joseph
Joseph

Reputation: 522

snowflake - get Quarter of a date format

I am trying to run this query to convert the timestamp column to its quarter equivalent:

SELECT TO_CHAR(LAST_MODIFIED_DATE, 'QUARTER') AS "X LABEL", Count(*) AS "X VALUE" FROM TABLE
 GROUP BY  TO_CHAR(LAST_MODIFIED_DATE,'QUARTER') ORDER BY  "X LABEL"

But my result set is not returning the proper quarter group (1-4) and instead is returning the count of the whole table.

Note that I need to use TO_CHAR as I cannot explicitly modify the query for the sake of Quarter format.

Sample output that I want to achieve:

X Label | XValue
--------+--------
1       + 10
2       + 15
3       + 20
4       + 25

Upvotes: 1

Views: 1748

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25968

use QUARTER

SELECT
    QUARTER(LAST_MODIFIED_DATE) AS "X LABEL", 
    Count(*) AS "X VALUE" 
FROM VALUES 
    ('2022-01-01'::date),
    ('2022-01-01'::date),
    ('2022-05-01'::date),
    ('2022-07-01'::date),
    ('2022-12-01'::date)
    t(last_modified_date)
GROUP BY  "X LABEL"

gives:

X LABEL X VALUE
1 2
2 1
3 1
4 1

DATE_TRUNC('QUARTER', date) can be useful also, or using YEAR(date), QUARTER(date)

Upvotes: 2

Related Questions