计算每月从星期一开始的星期 [英] Calculate week of month starting Monday
问题描述
我具有此功能,可以很好地计算月份中的星期-我需要它从星期一开始。
I have this function that works very well to calculate the week of month - i need it to start the week from Monday
CREATE FUNCTION dbo.ufs_FirstofMonth (@theDate DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN ( DATEADD(d, (DAY(@theDate)-1) * (-1) ,@theDate ) )
END
GO
CREATE FUNCTION dbo.ufs_FirstSunday (@theDate DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN ( DATEADD(d, CASE WHEN DATEPART ( dw , dbo.ufs_FirstofMonth(@theDate)) = 1 THEN 0
ELSE 8-DATEPART ( dw , dbo.ufs_FirstofMonth(@theDate))
END
, dbo.ufs_FirstofMonth(@theDate)) )
END
GO
CREATE FUNCTION dbo.ufs_WeekOfMonth (@theDate DATETIME)
RETURNS INTEGER
AS
BEGIN
RETURN (CASE WHEN DATEPART ( dw , @theDate) > DAY(@theDate)
THEN 1 + DATEDIFF(wk, dbo.ufs_FirstSunday(DATEADD(mm,-1,@theDate)) , @theDate)
ELSE 1 + DATEDIFF(wk, dbo.ufs_FirstSunday(@theDate) , @theDate)
END
)
END
推荐答案
这里有2种不同的方式,都在假设一周从星期一开始
Here are 2 different ways, both are assuming the week starts on monday
如果您希望整个星期都完整,那么它们属于开始的月份:因此,2012-09-01星期六和2012-星期日09-02是第4周,星期一2012-09-03是第1周,请使用以下命令:
If you want weeks to be whole, so they belong to the month in which they start: So saturday 2012-09-01 and sunday 2012-09-02 is week 4 and monday 2012-09-03 is week 1 use this:
declare @date datetime = '2012-09-01'
select datepart(day, datediff(day, 0, @date)/7 * 7)/7 + 1
如果您的周数在monthchange上减少,那么在2012-09-01星期六和2012-09-02星期天是第1周,在2012-09-03星期一是第2周,请使用:
If your weeks cut on monthchange so saturday 2012-09-01 and sunday 2012-09-02 is week 1 and monday 2012-09-03 is week 2 use this:
declare @date datetime = '2012-09-01'
select datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, @date), 0)), 0), @date - 1) + 1
这篇关于计算每月从星期一开始的星期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!