Is there a simple way to return the last day of the most recent quarter in Snowflake?

We use financial quarters with last days being: Jan. 31, Apr. 30, July 31, Oct. 31

Thanks!

CodePudding user response：

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;

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:

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..