SQL Server中的周数

Question

以下查询创建星期日至星期六

但我需要从周一到周日的几个星期

declare @begDate datetime
declare @endDate datetime
set @begDate = '01-APR-2020'
set @endDate = '30-APR-2020';
WITH N(n) AS  
(   
SELECT 0  
UNION ALL 
SELECT n+1
FROM N 
WHERE n <= datepart(dd,@enddate)
)
SELECT      
DATEADD(dd, n, @BegDate) AS dDate 
INTO
#Noofweeks 
FROM        
N
WHERE       
MONTH(DATEADD(dd, n, @BegDate)) = MONTH(@BegDate)
SELECT 
MIN(dDate) AS BegOfWeek,
MAX(dDate) AS EndOfWeek,
DATEDIFF(week, DATEADD(week, DATEDIFF(week, 0, DATEADD(month, datediff(month, 0, dDate), 0)), 0), dDate) AS WeekNumForMonth
FROM
#Noofweeks
GROUP BY
DATEDIFF(week, DATEADD(week, DATEDIFF(week, 0, DATEADD(month, datediff(month, 0, dDate), 0)), 0), dDate) 
ORDER BY
3, 1
DROP TABLE #Noofweeks

Answer 1

Hej，

也许试试这个

SET DATEFIRST 1;
declare @begDate datetime
declare @endDate datetime
set @begDate = '01-APR-2020'
set @endDate = '30-APR-2020';
WITH N(n) AS  
(   
SELECT 0  
UNION ALL 
SELECT n+1
FROM N 
WHERE n <= datepart(dd,@enddate)
)
SELECT      
DATEADD(dd, n, @BegDate) AS dDate 
INTO
#Noofweeks 
FROM        
N
WHERE       
MONTH(DATEADD(dd, n, @BegDate)) = MONTH(@BegDate)

;WITH CTE 
AS
(SELECT  DISTINCT
DATEPART(WEEK, ddate)  -
DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,ddate), 0))+ 1 AS WEEK_OF_MONTH,DATEPART(WEEK, ddate) as week_in_the_year  FROM #Noofweeks)
SELECT cte.WEEK_OF_MONTH,n.begining_of_week,n.end_of_week FROM cte join 
(SELECT DATEPART(WEEK, ddate) as week_in_the_year , MIN(ddate) as begining_of_week,MAX(ddate) end_of_week FROM #Noofweeks
GROUP BY DATEPART(WEEK, ddate)) as n on cte.week_in_the_year= n.week_in_the_year

DROP TABLE #Noofweeks

Answer 2

您可以设置一周的第一天：

DECLARE @datefirst int=@@DATEFIRST;
SET DATEFIRST 1;
--- Your code here
---
SET DATEFIRST @datefirst;