将Excel形式转换为SQL

一尘不染

将Excel形式转换为SQL

sql

我在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

谢谢

韦恩

阅读 214

2021-03-08

共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

2021-03-08