Last day of most recent financial quarter in Snowflake
Was not sure if you want the last day of current quarter or prior quarter, but this should be enough to get where you want to go:
SELECT current_date today,
date_trunc('quarter', today) as cur_qrt,
dateadd('day', -1, cur_qrt) as last_day_of_prior_qrt,
dateadd('day', -1, dateadd('quarter', 1, cur_qrt)) as last_day_of_current_qrt;
| TODAY | CUR_QRT | LAST_DAY_OF_PRIOR_QRT | LAST_DAY_OF_CURRENT_QRT |
|---|---|---|---|
| 2022-01-19 | 2022-01-01 | 2021-12-31 | 2022-03-31 |
Thus last day of prior quarter for any day is:
dateadd('day', -1, date_trunc('quarter', DAY)) as last_day_of_prior_qrt,
Hmm, how you framed the question, I don't see how your dates are what you want.. BUT if they are then that is good.
SELECT
to_date(column1) as day,
IFF( extract(month, day) in (1, 4, 7, 10),
add_months(dateadd('day', -1, date_trunc('quarter', day)), -2),
add_months(dateadd('day', -1, date_trunc('quarter', day)), 1)
) as last_day_of_prior_qrt,
date_trunc('quarter', day) as cur_qrt,
dateadd('day', -1, date_trunc('quarter', day)) as sim_sqt
from values
('2021-01-04'),('2021-02-04'),('2021-03-04'),('2021-04-04'),('2021-05-04'),('2021-06-04'),('2021-07-04'),('2021-08-04');
gives:
| DAY | LAST_DAY_OF_PRIOR_QRT | CUR_QRT | SIM_SQT |
|---|---|---|---|
| 2021-01-04 | 2020-10-31 | 2021-01-01 | 2020-12-31 |
| 2021-02-04 | 2021-01-31 | 2021-01-01 | 2020-12-31 |
| 2021-03-04 | 2021-01-31 | 2021-01-01 | 2020-12-31 |
| 2021-04-04 | 2021-01-31 | 2021-04-01 | 2021-03-31 |
| 2021-05-04 | 2021-04-30 | 2021-04-01 | 2021-03-31 |
| 2021-06-04 | 2021-04-30 | 2021-04-01 | 2021-03-31 |
| 2021-07-04 | 2021-04-30 | 2021-07-01 | 2021-06-30 |
| 2021-08-04 | 2021-07-31 | 2021-07-01 | 2021-06-30 |
and given 4th Jan 2021 is in the 2021 Q1 I don't see how the "prior" quarter id 2020 Q3 and thus the last day of is 2020-10-31, But again if that is the results you want..