时间戳

首页 > 解决方案 > SQL Server 中的周数

sql-server - SQL Server 中的周数

问题描述

下面的查询创建星期天到星期六

但我需要从周一到周日的几周

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

标签： sql-server

解决方案

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

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

推荐阅读