SQL Server query for finding weeknumber of a month considering thursday as week counter

Question

I need a week number of the month. The week needs to start on Thursday.

I have tried many answers but i the below conditions are not satifying it.

For example: the week number for the below date is

SL  Date        Weeknumber   
---------------------------
1) 21/09/2017 - 3
2) 28/09/2017 - 4
3) 23/03/2017 - 4
4) 29/06/2017 - 5
5) 15/03/2018 - 3
6) 28/03/2018 - 4
7) 29/03/2018 - 5

Thanks in advance

Answer 1

This should give you what you're looking for. Kind of crazy, I know, but it works:

create table #months (date date);

insert into #months
values ('20170921')
    , ('20170928')
    , ('20170323')
    , ('20170629')
    , ('20180315')
    , ('20180328')
    , ('20180329');

select date
    , case 
        when weeksdif * 7 <= daysdiff
            then weeksdif + 1
        else weeksdif
        end [week nr]
from (
    select *
        , wk - wk_startOfMonth [weeksdif]
        , DATEDIFF(DAY, startOfMonth, date) [daysdiff]
    from (
        select *
            , DATEPART(wk, startOfMonth) wk_startOfMonth
            , DATEPART(WEEKDAY, startOfMonth) wkd_startOfMonth
        from (
            select *
                , DATEPART(wk, date) wk
                , DATEPART(weekday, date) wkd
                , cast( (CAST(YEAR(date) as varchar(8)) 
                        + RIGHT('0' + CAST(month(date) as varchar(8)), 2) 
                        + '01') as date) startOfMonth
            from #months
            ) result_set
        ) result_set
    ) result_set
order by date;

Answer 2

Assuming your input table is like

TableOfDates([SL] int,[Date] date)

Following query will give correct weeknumbers

set datefirst 4
go

--create table TableOfDates([SL] int,[Date] datetime)
--insert into TableOfDates values 
--(1,'09-21-2017'),
--(2,'09-28-2017'),
--(3,'03-23-2017')

select 

    t.Date,DATEPART(wk,t.[Date])- DATEPART(wk,DATEADD(d,1,DATEADD(m,-1,EOMONTH(t.[Date]))))
from TableOfDates t

go
--drop table TableOfDates
set datefirst 7
go

For SQL SERVER 2008 version

you can use following query

select 

    t.Date,DATEPART(wk,t.[Date])- DATEPART(wk,cast(dateadd(m,datediff(m,0,t.date),0) as date))
from TableOfDates t

Explanation

Set Datefirst by default is set to 7. We direct SQL server to use thursday(4) as the start of week. See MSDN documentation on this.

now since weeknumbers are calculated from the start of the year, and we need monthly weeknumbers, we calculate weeknumbers for the current date and the start of the month and their difference gives monthly weeknumbers

In the end we set back the datefirst to the default value of 7

See attached output

Answer 3

Use DATAFIRST.

From MSDN:-

Sets the first day of the week to a number from 1 through 7.

╔═══════════════════════════╦══════════════════════════╗
║           Value           ║ First day of the week is ║
╠═══════════════════════════╬══════════════════════════╣
║ 1                         ║ Monday                   ║
║ 2                         ║ Tuesday                  ║
║ 3                         ║ Wednesday                ║
║ 4                         ║ Thursday                 ║
║ 5                         ║ Friday                   ║
║ 6                         ║ Saturday                 ║
║ 7 (default, U.S. English) ║ Sunday                   ║
╚═══════════════════════════╩══════════════════════════╝

so use the next code:-

Set DATEFIRST 4
DECLARE @DATE DATETIME
SET @DATE = '2017-09-21'

SELECT DATEPART(WEEK, @DATE)  -
    DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,@DATE), 0)) AS WEEK_OF_MONTH

Result:-

3