How to get week number of the month from the date in sql server 2008

Question

In SQL Statement in microsoft sql server, there is a built-in function to get week number but it is the week of the year.

Select DatePart(week, '2012/11/30') // **returns 48**

The returned value 48 is the week number of the year.

Instead of 48, I want to get 1, 2, 3 or 4 (week number of the month). I think the week number of the month can be achieved by modules with Month Number of this week. For e.g.

Select DATEPART(week, '2012/11/30')%MONTH('2012/11/30')

But I want to know is there other built-in functions to get WeekNumber of the month in MS SQL SERVER.

see this: http://stackoverflow.com/questions/4928038/tsql-calculate-week-number-of-the-month while your solution is the one that looks most elegant to me. +1 for that — nawfal, Oct 29 '12 at 05:32
@nawfal the solution posted in the question is not even close to working — t-clausen.dk, Oct 29 '12 at 08:37
how do you define weeknumber ? Which day of the week start the week (sunday or monday). Do you want half weeks to be contained in 2 different months, do you want to handle it like iso_week or only whole weeks? — t-clausen.dk, Oct 29 '12 at 08:40
I confessed that the answer in this question is wrong. I will post the correct answer after I finish solving. — Nay Lin Aung, Oct 29 '12 at 09:14
Sorry for necro. I needed something very similar, but all results I found are like @t-clausen.dk 's answer. My interpretation of the question above is that Jan 1 - Jan 7 would be week 1, Jan 8-14 week 2, etc. That's what I needed, and I came up with this: select (datepart(day, @date) - 1) / 7 % 7 + 1 — AlsoKnownAsJazz, May 30 '19 at 15:27

t-clausen.dk · Accepted Answer · 2022-02-16T09:55:43.447

67

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 date = '2012-09-01'
SELECT (day(datediff(d,0,@date)/7*7)-1)/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 date = '2012-09-01'
SELECT 
  datediff(ww,datediff(d,0,dateadd(m,datediff(m,7,@date),0)
    )/7*7,dateadd(d,-1,@date))+1

I received an email from Gerald. He pointed out a flaw in the second method. This should be fixed now

I received an email from Ben Wilkins. He pointed out a flaw in the first method. This should be fixed now

edited Feb 16 '22 at 09:55

answered Oct 29 '12 at 10:35

t-clausen.dk

43,517
12
59
92

didn't want to dv as I'd already upvoted - your 2nd solution returns week 6 for 31-mar-2014. this `select datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, @date), 0)), 0), @date)` returns 5 which is correct. – SteveB Apr 01 '14 at 15:06
although I am using SET DATEFIRST = 1 – SteveB Apr 01 '14 at 15:07
1

@bhs your datefirst should not affect this result at all. This result seems correct to me. There are 6 different weeks in march 2014 – t-clausen.dk Apr 01 '14 at 15:24
@t-clause.dk yes - you're right - apologies. In the report I am writing the sql for though they want it both ways - the week of the month but for yesterday to appear in week 5 :-). I think I'm going to 'persuade' them that dated weeks looks much better ;-) – SteveB Apr 01 '14 at 15:29
@t-clausen.dk There is a (@date - 1) in the second "formula" that causes a conversion error. It should be replaced with just "@date" if I'm reading it correctly. – Tony Fiorentini Aug 05 '15 at 21:01
@TonyFiorentini you have probably declared your column as date instead of datetime, that would cause a conversion error – t-clausen.dk Aug 06 '15 at 05:17
@t-clausen.dk When the first function is run for '2015-Nov-01', the function returns 4. When by your description it should return 6. – B Days Nov 24 '15 at 17:31
@BDays it returns 4 like intended according to the explanation – t-clausen.dk Nov 25 '15 at 08:26
So the function is flawed according to the explanation? How can '1-Nov-2015' be week 4 and the '30-Nov-2015' week5? Then you would have two sundays in that month reporting week4 – B Days Nov 25 '15 at 13:17
3

no it isn't flawed. It starts at the first whole week of the month. Week 1 starts at 2015-10-05(monday) It ends at the last week with a at least one day in the same month. Making sunday 2015-11-01 the last day in 4th week in october. As explained in the answer. Maybe you are confused by the text for the second answer – t-clausen.dk Nov 25 '15 at 13:21

score 13 · Answer 2 · answered Aug 06 '13 at 05:20

13

DECLARE @DATE DATETIME
SET @DATE = '2013-08-04'

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

answered Aug 06 '13 at 05:20

Passakorn

131
1
2

This does not work for second Sunday, it show in 3rd week. – Ankush Madankar Oct 26 '16 at 11:39
1

This works correctly in such that if a month end and begins mid week the proper week number is returned. ie. 2018-07-31 ends on a Tuesday, week 5. 2018-08-01 begins on a Wednesday, week 1. – mrbungle Jul 12 '18 at 17:45

GilM · Answer 3 · 2012-10-29T07:03:13.973

9

No built-in function. It depends what you mean by week of month. You might mean whether it's in the first 7 days (week 1), the second 7 days (week 2), etc. In that case it would just be

(DATEPART(day,@Date)-1)/7 + 1

If you want to use the same week numbering as is used with DATEPART(week,), you could use the difference between the week numbers of the first of the month and the date in question (+1):

(DATEPART(week,@Date)- DATEPART(week,DATEADD(m, DATEDIFF(m, 0, @Date), 0))) + 1

Or, you might need something else, depending on what you mean by the week number.

edited Oct 29 '12 at 07:03

answered Oct 29 '12 at 06:56

GilM

3,711
17
18

you should not use Datepart, it is not a reliable method. It relies of the setting for *DATEFIRST*. The result will be different on different SQL servers – t-clausen.dk Feb 16 '22 at 12:25

Bryan Focht · Answer 4 · 2013-12-30T22:50:30.937

7

Just look at the date and see what range it falls in.

Range 1-7 is the 1st week, Range 8-14 is the 2nd week, etc.

SELECT 
CASE WHEN DATEPART(day,yourdate) < 8 THEN '1' 
  ELSE CASE WHEN DATEPART(day,yourdate) < 15 then '2' 
    ELSE CASE WHEN  DATEPART(day,yourdate) < 22 then '3' 
      ELSE CASE WHEN  DATEPART(day,yourdate) < 29 then '4'     
        ELSE '5'
      END
    END
  END
END

edited Dec 30 '13 at 22:50

answered Dec 30 '13 at 03:42

Bryan Focht

1,004
1
11
10

this rely on a specific setting for DATEFIRST. It will not work on all systems – t-clausen.dk Feb 16 '22 at 09:48

score 3 · Answer 5 · answered Nov 10 '14 at 12:26

3

Similar to the second solution, less code:

declare @date datetime = '2014-03-31'
SELECT DATEDIFF(week,0,@date) - (DATEDIFF(week,0,DATEADD(dd, -DAY(@date)+1, @date))-1)

answered Nov 10 '14 at 12:26

DBADale

31
1

score 2 · Answer 6 · edited Mar 10 '14 at 07:51

2

Check this out... its working fine.

declare @date as datetime = '2014-03-10'
select DATEPART(week,@date) - DATEPART(week,cast(cast(year(@date) as varchar(4))+'-' + cast(month(@date) as varchar(2)) + '-01' as datetime))+1

edited Mar 10 '14 at 07:51

Baby Groot

4,637
39
52
71

answered Mar 10 '14 at 07:34

Sid

21
1

score 2 · Answer 7 · edited Aug 01 '19 at 11:39

2

WeekMonth = CASE WHEN (DATEPART(day,TestDate) - datepart(dw,TestDate))>= 22 THEN '5' 
                 WHEN (DATEPART(day,TestDate) - datepart(dw,TestDate))>= 15 THEN '4' 
                 WHEN (DATEPART(day,TestDate) - datepart(dw,TestDate))>= 8 THEN '3'
                 WHEN (DATEPART(day,TestDate) - datepart(dw,TestDate))>= 1 THEN '2' 
                 ELSE '1'
             END

edited Aug 01 '19 at 11:39

4b0

21,981
30
95
142

answered Aug 01 '19 at 11:37

Matricks

21
1

Seems that `datepart(dw,TestDate)` returns by default [1 for Sunday](https://stackoverflow.com/questions/24877124/sql-datepartdw-date-need-monday-1-and-sunday-7), so it can move sundays one week later without proper configuration. Otherwise +1 for considering that not all months start with monday – barbsan Aug 01 '19 at 13:01

score 1 · Answer 8 · answered Oct 29 '12 at 05:36

1

There is no inbuilt function to get you the week number. I dont think dividing will help you anyway as the number of weeks in a month is not constant.

http://msdn.microsoft.com/en-us/library/bb675168.aspx

I guess you can divide the number(48) by 4 and take the modules of the same and project that as the week number of that month, by adding one to the result.

answered Oct 29 '12 at 05:36

Anil

967
10
20

Friend, thanks for your answer but it's wrong. For example, if week number of '2011/11/20' (YYYY/MM/DD) is divided by 4, the module "0" will be out. In addition, I confessed that my answer is not correct so that I used the term "I think". – Nay Lin Aung Oct 29 '12 at 06:03

score 1 · Answer 9 · edited Jul 09 '13 at 22:05

Here's a suggestion for getting the first and last days of the week for a month:

-- Build a temp table with all the dates of the month 
drop table #tmp_datesforMonth 
go

declare @begDate datetime
declare @endDate datetime

set @begDate = '6/1/13'
set @endDate = '6/30/13';

WITH N(n) AS  
(   SELECT 0  
        UNION ALL 
    SELECT n+1 
    FROM N 
    WHERE n <= datepart(dd,@enddate)
)
SELECT      DATEADD(dd,n,@BegDate) as dDate 
into #tmp_datesforMonth
FROM        N
WHERE       MONTH(DATEADD(dd,n,@BegDate)) = MONTH(@BegDate)

--- pull results showing the weeks' dates and the week # for the month (not the week # for the current month) 

select  MIN(dDate) as BegOfWeek
, MAX(dDate) as EndOfWeek 
, datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, dDate), 0)), 0), dDate) as WeekNumForMonth 
from #tmp_datesforMonth
group by datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, dDate), 0)), 0), dDate) 
order by 3, 1

score 1 · Answer 10 · answered Aug 22 '14 at 19:49

1

A dirty but easy one liner using Dense_Rank function. Performance WILL suffer, but effective none the less.

DENSE_RANK()over(Partition by Month(yourdate),Year(yourdate) Order by Datepart(week,yourdate) asc) as Week

answered Aug 22 '14 at 19:49

Brennan Webb

11
2

score 1 · Answer 11 · edited Oct 21 '16 at 13:39

1

floor((day(@DateValue)-1)/7)+1

edited Oct 21 '16 at 13:39

John Smith

7,243
6
49
61

answered Nov 26 '14 at 01:14

Peter

51
5

score 1 · Answer 12 · edited Oct 21 '16 at 13:39

1

Here you go....

Im using the code below..

DATEPART(WK,@DATE_INSERT) - DATEPART(WK,DATEADD(DAY,1,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DATE_INSERT),0)))) + 1

edited Oct 21 '16 at 13:39

John Smith

7,243
6
49
61

answered Dec 01 '14 at 12:49

Jonathan Takeishi

11
1

score 1 · Answer 13 · edited May 23 '17 at 12:26

Here is the query that brings the week number on whatever the startday and endday of the week it may be.

SET DATEFIRST 2    

DECLARE @FROMDATE DATE='12-JAN-2015'
-- Get the first day of month
DECLARE @ALLDATE DATE=DATEADD(month, DATEDIFF(month, 0, @FROMDATE), 0)
DECLARE @FIRSTDATE DATE


;WITH  CTE as
(
     -- Get all dates in that month
     SELECT 1 RNO,CAST(@ALLDATE AS DATE) as DATES 
     UNION ALL
     SELECT RNO+1, DATEADD(DAY,1,DATES )
     FROM    CTE
     WHERE   DATES < DATEADD(MONTH,1,@ALLDATE)
)
-- Retrieves the first day of week, ie, if first day of week is Tuesday, it selects first Tuesday 
SELECT TOP 1 @FIRSTDATE =   DATES 
FROM    CTE 
WHERE DATEPART(W,DATES)=1

SELECT (DATEDIFF(DAY,@FIRSTDATE,@FROMDATE)/7)+1 WEEKNO

For more information I have answered for the below question. Can check that.

How do I find week number of a date according to DATEFIRST

P. Sandip · Answer 14 · 2018-03-15T12:42:15.387

1

Try Below Code:

declare @dt datetime='2018-03-15 05:16:00.000'
IF (Select (DatePart(DAY,@dt)%7))>0
  Select  (DatePart(DAY,@dt)/7) +1
ELSE
  Select  (DatePart(DAY,@dt)/7)

edited Mar 15 '18 at 12:42

answered Mar 15 '18 at 10:47

P. Sandip

21
9

score 1 · Answer 15 · answered Sep 09 '19 at 06:10

1

There is an inbuilt option to get the week number of the year

**select datepart(week,getdate())**

answered Sep 09 '19 at 06:10

asif juneja

61
2

score 0 · Answer 16 · edited Dec 01 '16 at 00:52

0

You can simply get week number by getting minimum week number of month and deduct it from week number. Suppose you have a table with dates

select
    emp_id, dt , datepart(wk,dt) - (select min(datepart(wk,dt))
from
    workdates ) + 1 from workdates

edited Dec 01 '16 at 00:52

Samsquanch

8,866
12
50
89

answered Dec 21 '15 at 11:41

Raj Dhawan

1
1

Ankita Gavhane · Answer 17 · 2018-03-15T12:53:07.200

0

Solution:

declare @dt datetime='2018-03-31 05:16:00.000'
IF (Select (DatePart(DAY,@dt)%7))>0
Select  (DatePart(DAY,@dt)/7) +1
ELSE
Select  (DatePart(DAY,@dt)/7)

edited Mar 15 '18 at 12:53

answered Mar 15 '18 at 11:36

Ankita Gavhane

1
1

score 0 · Answer 18 · answered Feb 02 '19 at 12:32

0

declare @end_date datetime = '2019-02-28';
select datepart(week, @end_date) - datepart(week, convert(datetime, substring(convert(nvarchar, convert(datetime, @end_date), 127), 1, 8) + '01')) + 1 [Week of Month];

answered Feb 02 '19 at 12:32

Stack Trader

1

3

You should add some explanation. – Ahmad Khan Feb 02 '19 at 13:02
2

More explanation would make it easier for the question asker to understand what you are trying to convey. I would suggest going back an adding additional detail as to why this is the right answer for the question. – Andrew Feb 02 '19 at 15:37

score 0 · Answer 19 · edited May 08 '19 at 10:38

0

Here is the tried and tested solution for this query in any situation - like if 1st of the month is on Friday , then also this will work -

select (DATEPART(wk,@date_given)-DATEPART(wk,dateadd(d,1-day(@date_given),@date_given)))+1

above are some solutions which will fail if the month's first date is on Friday , then 4th will be 2nd week of the month

edited May 08 '19 at 10:38

Mickael Maison

25,067
7
71
68

answered May 08 '19 at 10:12

Factor

21
1
3

Jeremy Buentello · Answer 20 · 2019-07-22T18:17:27.493

0

Logic here works as well 4.3 weeks in every month. Take that from the DATEPART(WEEK) on every month but January. Just another way of looking at things. This would also account for months where there is a 5th week

DECLARE @date VARCHAR(10)
SET @date = '7/27/2019'

SELECT CEILING(DATEPART(WEEK,@date)-((DATEPART(MONTH,@date)-1)*4.3333)) 'Week of Month'

edited Jul 22 '19 at 18:17

answered Jul 22 '19 at 18:04

Jeremy Buentello

133
9

score 0 · Answer 21 · edited Jun 18 '21 at 07:25

0

Below will only work if you have every week of the month represented in the select list. Else the rank function will not work, but it is a good solution.

SELECT DENSE_RANK() OVER (PARTITION BY MONTH(DATEFIELD) 
                          ORDER BY DATEPART(WEEK,DATEFIELD) ASC) AS WeekofMont

edited Jun 18 '21 at 07:25

TT.

15,774
6
47
88

answered Jun 18 '21 at 07:11

Nico Pretorius

1

Thanks TT. the Edit looks a lot better! – Nico Pretorius Jun 21 '21 at 10:45

score 0 · Answer 22 · answered Oct 08 '21 at 00:59

0

try this one

declare @date datetime = '20210928'

select convert(int,(((cast(datepart(day,@date) as decimal(4,2))/7)-(1.00/7.00))+1.00))

answered Oct 08 '21 at 00:59

Omar Abokhadra

1

chenZ · Answer 23 · 2022-11-08T03:56:30.393

select datepart(week,@date)-datepart(week,dateadd(day,1,eomonth(dateadd(m,-1,@date))))+1

or

select datepart(week,@date)-datepart(week,dateadd(d,-datepart(d,@date)+1,@date))+1

steps:
1,get the first day of month
2,week of year of the date - week of year of the first day of the month
3,+1

2023-1-1 is sunday

SET DATEFIRST 1;
DECLARE @date date = '2023-1-02';
select datepart(week,@date)-datepart(week,dateadd(day,1,eomonth(dateadd(m,-1,@date))))+1

return 2

SET DATEFIRST 7;
DECLARE @date date = '2023-1-02';
select datepart(week,@date)-datepart(week,dateadd(day,1,eomonth(dateadd(m,-1,@date))))+1

return 1

score 0 · Answer 24 · answered Jul 11 '23 at 12:33

0

You can try below statement.

select datepart(week, getdate()) - datepart(week, DATEADD(ms,0,DATEADD(mm, DATEDIFF(m,0,getdate()),0))) + 1

answered Jul 11 '23 at 12:33

Nitin Goswami

1

Thank you for your interest in contributing to the Stack Overflow community. This question already has quite a few answers—including one that has been extensively validated by the community. Are you certain your approach hasn’t been given previously? **If so, it would be useful to explain how your approach is different, under what circumstances your approach might be preferred, and/or why you think the previous answers aren’t sufficient.** Can you kindly [edit] your answer to offer an explanation? – Jeremy Caney Jul 13 '23 at 03:27

score -1 · Answer 25 · edited Feb 05 '16 at 12:36

-1

Code is below:

set datefirst 7
declare @dt datetime='29/04/2016 00:00:00'
select (day(@dt)+datepart(WEEKDAY,dateadd(d,-day(@dt),@dt+1)))/7

edited Feb 05 '16 at 12:36

Musakkhir Sayyed

7,012
13
42
65

answered Feb 05 '16 at 11:47

Paulo Fio

1
1

score -1 · Answer 26 · answered Nov 12 '19 at 06:49

-1

select @DateCreated, DATEDIFF(WEEK, @DateCreated, GETDATE())

answered Nov 12 '19 at 06:49

shahida

327
3
9

How to get week number of the month from the date in sql server 2008

26 Answers26

Linked