I need to round dates to the closest month start. For example, I would like 2022-08-30 and 2022-09-03 to both evaluate to 2022-09-01.

How can I do this with Snowflake SQL?

CodePudding user response：

We can encapsulate this logic into a SQL UDF:

create or replace function round_to_month(d date)
returns date
as $$
select x 
from (
    select date_trunc(month, d) before
        , dateadd(month, 1, before) after      
        , iff(d-before < after-d, before, after) x
)
$$

Sample:

with data(d) as (
    select $1::date
    from values('2020-01-10'), ('2020-01-20'), ('2020-01-16'), ('2020-01-17')
)

select *, round_to_month(d)
from data

CodePudding user response：

Approach 1: Adding 1 month to beginning of month if day of date is greater than midpoint:

WITH cte(d) AS (
    SELECT s.d::DATE
    FROM VALUES('2020-01-10'), ('2020-01-20'), ('2020-01-16'), 
               ('2020-01-17'), ('2022-02-14') AS s(d)
)
SELECT d
      ,DATEADD('MONTH'
               ,(DAY(d) > CEIL(DAY(LAST_DAY(d)))/2)::INT
               ,DATE_TRUNC('MONTH', d)
              ) AS round_to_month
FROM cte;

Output:

Approach 2:

Snowflake provides TIME_SLICE function which is the easiest way to get start of current/next month:

SELECT '2022-10-13'::DATE AS d
      ,TIME_SLICE(d, 1, 'MONTH', 'START') AS prev
      ,TIME_SLICE(d, 1, 'MONTH', 'END') AS next

Output:

Using the same idea as Felipe's answer:

WITH cte(d) AS (
    SELECT s.d::DATE
    FROM VALUES('2020-01-10'), ('2020-01-20'), ('2020-01-16'),
               ('2020-01-17'), ('2022-02-14') AS s(d)
)
SELECT d,
  CASE WHEN d-TIME_SLICE(d, 1, 'MONTH', 'START') < TIME_SLICE(d, 1, 'MONTH', 'END')-d
       THEN TIME_SLICE(d, 1, 'MONTH', 'START')
       ELSE TIME_SLICE(d, 1, 'MONTH', 'END')
  END AS rount_to_month
FROM cte;