sql server 取当天所在第几周

DECLARE @date DATETIME
SET @date='2023-06-01'

-- 一周的第一天
SELECT DATEADD(DAY,2-DATEPART(weekday,@date),@date)

--一周的最后一天
SELECT DATEADD(DAY,8-DATEPART(WeekDay,@date),@date)

--日期所在周
select DATENAME(WEEK,@date)

declare @datetime datetime;;
set @datetime=getdate();
select dateadd(day,1-(datepart(weekday,@datetime)),@datetime) as dStartDate, ----取本周开始日期
dateadd(day,1-(datepart(weekday,@datetime))+6,@datetime) as dEndDate,----取本周截至日期
datepart(year,dateadd(day,1-(datepart(weekday,@datetime)),@datetime)) as iYear,----取所在月份
datepart(month,dateadd(day,1-(datepart(weekday,@datetime)),@datetime)) as iMonth,----取所在月份
datepart(day,dateadd(day,1-(datepart(weekday,@datetime)),@datetime))/7 + 1 as iWeekNum ----所在月份共几周