snwoflake weird date issue with rolling quarters on custom period

Question

I can't calculate rolling quarters with a custom period table:

Period
2017-06-01
2020-12-01
2020-11-01

Rolling month and years are working:

  DATEADD(MONTH, -2, "Period") AS "Period M-2"
  DATEADD(YEAR, -1, "Period") AS "Period Y-1"

When i query quarters with current_date() this is working:

SELECT DATE_TRUNC('MONTH',DATEADD(QUARTER, -1, current_date()))

When i query my table with

DATEADD(QUARTER, -1, "Period")  AS "Period Q-1"

I have the following results (it seems calculating M-3 instead quarters):

Period	Period Q-1
2017-08-01	2017-05-01
2017-09-01	2017-06-01
2017-10-01	2017-07-01

To reproduce the issue:

WITH input AS (select $1 "Period" from values 
('2017-08-01'),
('2017-09-01'),
('2017-10-01'),
('2017-05-01'),
('2020-11-01'),
('2020-11-01'),
('2017-04-01'),
('2020-10-01'),
('2020-10-01')
)
SELECT "Period",
DATE(DATEADD(MONTH, -1, "Period" )) AS  "Period M-1",
DATE(DATEADD(MONTH, -2, "Period" )) AS  "Period M-2",
DATE(DATEADD(QUARTER, -1, "Period" )) AS  "Period Q-1",
DATE(DATEADD(QUARTER, -2, "Period" )) AS  "Period Q-2",
DATE(DATEADD(YEAR, -1, "Period" )) AS  "Period Y-1",
DATE(DATEADD(YEAR, -2, "Period" )) AS  "Period Y-2"
FROM input

UPDATE: This is working with this formula, thank you for explanations @Francesco.

  DATE_TRUNC('MONTH',DATEADD('DAY', -1, DATE_TRUNC('QUARTER', "Period"))) AS "Period Q1"

But Q2 & Q3 still not working.

Answer 1

Note that

DATEADD(QUARTER, -<value>, <date> )

is equivalent to

DATEADD(MONTH, -<value>, <date> )

whereas the following returns the timestamp corresponding to midnight of the first day of the quarter for the input timestamp (see https://docs.snowflake.com/en/sql-reference/functions/date_trunc.html)

DATE_TRUNC('QUARTER', <date> )

Hence, as an example, the following

SELECT DATE_TRUNC('QUARTER', DATEADD(QUARTER, -1, '2021-03-29'));

is returing 2020-10-01 00:00:00.000.

If the aim is to return the first day of last month for previous quarters (for example for period =2020-04-01 that would be Q-1 = 2020-03-01, Q-2 = 2019-12-01, Q-3= 2019-09-01):

SELECT 
    DATEADD(MONTH, -1, DATEADD(QUARTER, 1
                                , DATE_TRUNC('QUARTER', DATEADD(QUARTER, -1, <date>))
                                )
                   ) AS "Q-1",
    DATEADD(MONTH, -1, DATEADD(QUARTER, 1
                                , DATE_TRUNC('QUARTER', DATEADD(QUARTER, -2, <date>))
                                )
                   ) AS "Q-2",
    DATEADD(MONTH, -1, DATEADD(QUARTER, 1
                                , DATE_TRUNC('QUARTER', DATEADD(QUARTER, -3, <date>))
                                )
                   ) AS "Q-3"
;