sql - 将 Excel 公式转换为 SQL

Question

我在 Excel 中有一个公式可以计算两个日期之间有多少个星期天，但我想在 SQL 中做同样的事情，但不知道该怎么做。你能帮我吗？

=SUM(INT((WEEKDAY(A1-8,1)+B1-A1)/7))

Declare @From as Datetime 
Declare @To as Datetime 

Set @From = '7 Jan 2013' 
Set @To = '18 Mar 2013' 

SELECT SUM(WEEKDATE(@From-8,1)+ @End-@From)/7)) AS No_Of_Sundays 
FROM TIME

谢谢

韦恩

Answer 1

尝试这个:

SELECT SUM((CASE WHEN DATEPART(dw, @From) = 7 THEN 1 ELSE 0 END) + DATEDIFF(dd, @From, @To) / 7) AS No_Of_Sundays 

如您所见，这取决于本地化 -> 在俄罗斯，星期日是第 7 天，所以我们检查第一天是否是星期日

这是另一个解决方案:

Declare @From as Datetime 
Declare @To as Datetime 
Declare @sundays as INT

Set @From = '1/02/2013' 
Set @To = '28/02/2013' 
Set @sundays = 0

WHILE (@From <= @To)
BEGIN
    SET @sundays = @sundays + (CASE WHEN DATEPART(dw, @From) = 7 THEN 1 ELSE 0 END)
    SET @From = DATEADD(dd, 1, @From)
END

SELECT @sundays