--计算给定日期是当月的第几周 DECLARE @Date DATETIME; SET @Date = GETDATE() --思路,给定日期是当年的第几周-给定日期所在月第一天是当年的第几周 SELECT DATEPART(WEEK,@Date)-DATEPART(WEEK,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0))+1 [WeekOfMonth] SELECT DATEPART(WEEK,@Date)-DATEPART(WEEK,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))+1 [WeekOfMonth] GO
select cast(datename(week,getdate()) as int)+1 -cast(datename(week,(select dateadd(day,-(SELECT DATEPART(day, getdate())),getdate()))) as int)
新手写的,很容易看懂DECLARE @Date DATETIME SET @Date = GETDATE() select case when datepart(dd,GETDATE())/7>0 then datepart(dd,GETDATE())/7+1 when datepart(dd,GETDATE())/7=0 then datepart(dd,GETDATE())/7 end Weekofmonth
DECLARE @Date DATETIME;
SET @Date = GETDATE()
--思路,给定日期是当年的第几周-给定日期所在月第一天是当年的第几周
SELECT DATEPART(WEEK,@Date)-DATEPART(WEEK,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0))+1 [WeekOfMonth]
SELECT DATEPART(WEEK,@Date)-DATEPART(WEEK,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))+1 [WeekOfMonth]
GO
select cast(datename(week,getdate()) as int)+1
-cast(datename(week,(select dateadd(day,-(SELECT DATEPART(day, getdate())),getdate()))) as int)
SET @Date = GETDATE()
select case when datepart(dd,GETDATE())/7>0 then datepart(dd,GETDATE())/7+1
when datepart(dd,GETDATE())/7=0 then datepart(dd,GETDATE())/7 end Weekofmonth