How can I specify 4 different start and end dates for previous year and current year all specifying the 1st day of the month using SET?

Question

I am trying to make SET variables in SnowSQL that will specify the first of each start month and end month for the current year and previous years respectively. I want the code to be able to roll over to the new month as time progresses. So for example, since it is June 2020 we would have m2_start = 2020-06-01 and m2_end = 2020-07-01. m1_start = 2019-06-01 and m1_end 2019-07-01. Here is what I have so far, but it is not quiet what I want as I can't figure out how to specify on the first of the month:

SET m1_start = (SELECT DISTINCT DATEADD(year,-1,CURRENT_DATE) FROM DIMDATE d WHERE DAY(d."Date") = 1);

SET m1_end = (SELECT DISTINCT DATEADD(month, -11,CURRENT_DATE) FROM DIMDATE d WHERE DAY(d."Date") = 1);

SET m2_start = (SELECT d."Date" FROM DIMDATE d WHERE DAY(d."Date") = 1 AND MONTH(d."Date") = MONTH(CURRENT_DATE) AND YEAR(d."Date") = YEAR(CURRENT_DATE));

SET m2_end = (SELECT DISTINCT DATEADD(month, 1,CURRENT_DATE) FROM DIMDATE d WHERE DAY(d."Date") = 1);

SET Test = '2020-06-01';

Answer 1

This can be simplified using DATE_ADD(…), DATE_TRUNC(…) and ADD_MONTHS(…) purpose-specific date/time functions.

I can't figure out how to specify on the first of the month:

The DATE_TRUNC(…) function lets you chop-down any date to the beginning of any of its parts. When run with the month part, it will yield a date equal to the beginning of the month.

A minimal example:

SET _current_date = (SELECT CURRENT_DATE);
SET _last_year_date = (SELECT DATEADD(year, -1, $_current_date));

SET m2_start = (SELECT DATE_TRUNC(month, $_current_date));
SET m2_end = (SELECT ADD_MONTHS($m2_start, 1));

SET m1_start = (SELECT DATE_TRUNC(month, $_last_year_date));
SET m1_end = (SELECT ADD_MONTHS($m1_start, 1));

-- Alternatively, you can derive m1_* values by subtracting a year from m2_*
-- values, but it felt too intermingled
-- SET m1_start = (SELECT DATE_ADD(year, -1, $m2_start));
-- SET m1_end = (SELECT DATE_ADD(year, -1, $m2_end));

Example output when executed on 2020-06-24:

> SELECT $_current_date, $_last_year_date, $m1_start, $m1_end, $m2_start, $m2_end;

+----------------+------------------+------------+------------+------------+------------+
| $_CURRENT_DATE | $_LAST_YEAR_DATE | $M1_START  | $M1_END    | $M2_START  | $M2_END    |
|----------------+------------------+------------+------------+------------+------------|
| 2020-06-23     | 2019-06-23       | 2019-06-01 | 2019-07-01 | 2020-06-01 | 2020-07-01 |
+----------------+------------------+------------+------------+------------+------------+