CODEWITHSUNDEEP
sql-serverdatetime
user2871996
user2871996

Reputation: 43

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

Upvotes: 0

Views: 2123

Answers (2)

user3272686
user3272686

Reputation: 849

Here are 2 different ways, both are assuming the week starts on monday

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

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

Upvotes: 3

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

Add the following as the first line in your functions:

SET DATEFIRST 1 -- Set Monday as the first day of the week

Ref. SET DATEFIRST (Transact-SQL) - Sets the first day of the week to a number from 1 through 7. The U.S. English default is 7, Sunday.

Upvotes: 0

Related Questions