计算每月从星期一开始的星期 [英] 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屋！