雪花中两个日期之间的工作日 [英] Working days between two dates in Snowflake

问题描述

是否有任何方法可以仅使用"datediff"来计算雪花中两个日期之间的工作日，而无需创建日历表?功能

解决方案

在研究了雪花datediff函数之后，我发现了以下结论.

1. DATEDIFF(DAY/WEEK，START_DATE，END_DATE)将计算差异，但最后一个日期将被视为END_DATE -1.
2. DATEDIFF(星期，START_DATE，END_DATE)将计算两个日期之间的星期日的数量.

通过总结这两点，我实现了下面的逻辑.

  SELECT(DATEDIFF(DAY，START_DATE，DATEADD(DAY，1，END_DATE))-DATEDIFF(星期，START_DATE，DATEADD(DAY，1，END_DATE))* 2-(在DAYNAME(START_DATE)！='Sun'THEN 1 ELSE 0 END的情况下)+(如果NAME为DAYNAME(END_DATE)！='Sat'THEN 1 ELSE 0 END))AS WORKING_DAYS 

Is there any ways to calculate working days between two dates in snowflake without creating calendar table, only using "datediff" function

解决方案

After doing research work on snowflake datediff function, I have found the following conclusions.

1. DATEDIFF(DAY/WEEK, START_DATE, END_DATE) will calculate difference, but the last date will be considered as END_DATE -1.
2. DATEDIFF(WEEK, START_DATE, END_DATE) will count number of Sundays between two dates.

By summarizing these two points, I have implemented the logic below.

SELECT 
    ( DATEDIFF(DAY, START_DATE, DATEADD(DAY, 1, END_DATE))
    - DATEDIFF(WEEK, START_DATE, DATEADD(DAY, 1, END_DATE))*2 
    - (CASE WHEN DAYNAME(START_DATE) != 'Sun' THEN 1 ELSE 0 END)
    + (CASE WHEN DAYNAME(END_DATE) != 'Sat' THEN 1 ELSE 0 END)
    ) AS WORKING_DAYS   

这篇关于雪花中两个日期之间的工作日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！