I have written the "select" query for the quarter function in SAP HANA.
select QUARTER (CURRENT_DATE, 8) FROM DUMMY;
output: 2021-Q3
Can someone please help me with the equivalent query in Snowflake?
Quarter function in Snowflake
Viewed 419
3 Answers
Try any of these:
SELECT DATE_TRUNC('QUARTER', CURRENT_TIMESTAMP())
, QUARTER(CURRENT_TIMESTAMP())
, YEAR(CURRENT_TIMESTAMP()) || '-Q' || QUARTER(CURRENT_TIMESTAMP());
The goal is to handle second argument indicating start month of fiscal year.
QUARTER (d, [, start_month ])
For current date(2022-02-06) the output should not be 2022-Q1 but 2021-Q3.
It could be achieved with some date computations:
- first part is handling year(current/previous)
- second part is computing quarter adjusted with start month
Query:
SET FirstMonth = 8;
SELECT YEAR(d) - IFF(d >= DATEFROMPARTS(YEAR(d),$FirstMonth,1), 0, 1)
|| '-Q' || QUARTER(DATEADD(month, -$FirstMonth+1, d)) AS output
FROM (SELECT CURRENT_DATE() AS d) AS sub;
Output: 2021-Q3
As Lukasz points out the second parameter is the start_month SAP doc's
So this is really two parts, to know what year-quarter something is with respect to an offset, you just need to subtract the offset month, from the date you have and then year and quarter the adjusted date. AND formatting the STRING
The later point it seems cannot be done with one TO_CHAR with a format command. otherwise it would be really nice. So for now we will have to stick to YEAR(adj_date) || '-Q' || QUARTER(adj_date).
The long winded explaining SQL:
SELECT
8 as first_month_of_fin_year
,first_month_of_fin_year - 1 as month_adj
,to_date(column1,'yyyy-mm') as in_date
,dateadd(month,-month_adj, in_date) as adj_date
,year(adj_date) as y
,quarter(adj_date) as q
,y::text || '-Q' || q::text as answer
FROM VALUES
('2021-06'),
('2021-07'),
('2021-08'),
('2021-09'),
('2021-10'),
('2021-11'),
('2021-12'),
('2022-01'),
('2022-02'),
('2022-03'),
('2022-04'),
('2022-05'),
('2022-06'),
('2022-07'),
('2022-08'),
('2022-09'),
('2022-10')
ORDER BY 1;
gives:
| FIRST_MONTH_OF_FIN_YEAR | MONTH_ADJ | IN_DATE | ADJ_DATE | Y | Q | ANSWER |
|---|---|---|---|---|---|---|
| 8 | 7 | 2021-06-01 | 2020-11-01 | 2020 | 4 | 2020-Q4 |
| 8 | 7 | 2021-07-01 | 2020-12-01 | 2020 | 4 | 2020-Q4 |
| 8 | 7 | 2021-08-01 | 2021-01-01 | 2021 | 1 | 2021-Q1 |
| 8 | 7 | 2021-09-01 | 2021-02-01 | 2021 | 1 | 2021-Q1 |
| 8 | 7 | 2021-10-01 | 2021-03-01 | 2021 | 1 | 2021-Q1 |
| 8 | 7 | 2021-11-01 | 2021-04-01 | 2021 | 2 | 2021-Q2 |
| 8 | 7 | 2021-12-01 | 2021-05-01 | 2021 | 2 | 2021-Q2 |
| 8 | 7 | 2022-01-01 | 2021-06-01 | 2021 | 2 | 2021-Q2 |
| 8 | 7 | 2022-02-01 | 2021-07-01 | 2021 | 3 | 2021-Q3 |
| 8 | 7 | 2022-03-01 | 2021-08-01 | 2021 | 3 | 2021-Q3 |
| 8 | 7 | 2022-04-01 | 2021-09-01 | 2021 | 3 | 2021-Q3 |
| 8 | 7 | 2022-05-01 | 2021-10-01 | 2021 | 4 | 2021-Q4 |
| 8 | 7 | 2022-06-01 | 2021-11-01 | 2021 | 4 | 2021-Q4 |
| 8 | 7 | 2022-07-01 | 2021-12-01 | 2021 | 4 | 2021-Q4 |
| 8 | 7 | 2022-08-01 | 2022-01-01 | 2022 | 1 | 2022-Q1 |
| 8 | 7 | 2022-09-01 | 2022-02-01 | 2022 | 1 | 2022-Q1 |
| 8 | 7 | 2022-10-01 | 2022-03-01 | 2022 | 1 | 2022-Q1 |
Smaller SQL
So a smaller/compact SQL can be written once we see what steps we are following as:
SELECT
8 as start_month
,to_date(column1,'yyyy-mm') as in_date
,year(dateadd(month,-(start_month - 1), in_date)) || '-Q'
|| quarter(dateadd(month,-(start_month - 1), in_date)) as answer
FROM ...
or if you know you only want 8, and current_date
SELECT
year(dateadd(month,-7, current_date)) || '-Q'
|| quarter(dateadd(month,-7, current_date)) as answer
;
Related