如何将日期四舍五入到最近的月份

Question

我需要将日期四舍五入到最近的月份开始。例如，我希望2022-08-30和2022-09-03都评估为2022-09-01。

如何使用Snowflake SQL执行此操作？

Answer 1

我们可以将此逻辑封装到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
)
$$

样品：

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

Answer 2

方法1：如果日期大于中点，则在月初添加1个月：

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;

输出：

D	ROUND_TO_MONTH
2020-01-10	2020-01-01
2020-01-20	2020-02-01
2020-01-16	2020-02-01
2020-01-17	2020-02-01
2022-02-14	2022-02-01

使用@Lukasz的样本数据的另一种选择——如果结果日期为<第15位。然后，我们将1天添加到生成的last_day()中，以获得最接近的月初。

正如@Felipe在评论中所回避的那样，当2月有28天时，本月15日比2月1日更接近3月1日。为了说明这一点，我们可以有条件地减去14天。

select d, 
last_day(d-15)+1, -- if we assume 15th to be the midpoint for all months
last_day(d-iff(day(last_day(d))=28,14,15))+1 -- if we want to adjust for Feb 28
from cte;

我会选择一种更简单的方法，比如：

SELECT d, CASE
WHEN DAY(d) >= 15 THEN DATEADD('MONTH',1,DATE_TRUNC('MONTH',d))
ELSE DATE_TRUNC('MONTH',d)
END

基本上，如果日期低于15日，则提取约会对象的月份；如果日期大于15日，请在约会月份中添加一个月。