# get week number of month

• I want to query a table that has a datetime column ([CreateDateTime])  and get the week number for that specific month.

2023-03-01 09:30:00

2023-03-12 11:15:22

2023-03-22 14:22:18

2023-04-02 15:12:30

I want to get these results from the above dates:

1

3

4

1

• SET DATEFIRST 6 ; -- Set first Day of Week to Saturday

SELECT [CreateDateTime],DATEPART(WEEK,[CreateDateTime]) - DATEPART(WEEK,CAST(CAST(YEAR([CreateDateTime]) AS VARCHAR(4))+'-' + CAST(MONTH([CreateDateTime]) AS VARCHAR(2)) + '-01' AS DATETIME))+1 AS WeekNo

FROM yourtable

• How do you define "week number"?  Are the first 7 days of the month "week 1", or is it based on specific day of the week?  For example, all weeks are from Friday thru Thursday.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• I would define first week as any days of the month that land on the first week of the month.  i.e. if September 1 lands on Friday, then September 1 and 2 would be week 1 and week 2 starts on Sunday, September 3.  Sunday being the beginning of a week and Saturday the end of a week.

• GrassHopper wrote:

I would define first week as any days of the month that land on the first week of the month.  i.e. if September 1 lands on Friday, then September 1 and 2 would be week 1 and week 2 starts on Sunday, September 3.  Sunday being the beginning of a week and Saturday the end of a week.

You say that a new week starts on a Sunday, but in your sample results, you are expecting 1 to be returned for 2023-04-02 15:12:30. Is this a mistake?

Here is some sample code which generates a range of dates (thank you, Jeff) and then calculates week number for them.

--Edit: don't use this code – it works in most cases, but not all. See Jeff's solution below.

`DROP TABLE IF EXISTS #SomeDates;DECLARE @StartDate DATETIME       ,@EndDate DATETIME       ,@Days INT;SELECT @StartDate = '20230301'      ,@EndDate = '20230501'      ,@Days = DATEDIFF (dd, @StartDate, @EndDate);CREATE TABLE #SomeDates(    SomeDate DATE NOT NULL);INSERT #SomeDates(    SomeDate)SELECT TOP(@Days)       TheDate = DATEADD (dd, ROW_NUMBER () OVER (ORDER BY(SELECT NULL)) - 1, @StartDate)FROM sys.all_columns ac1    CROSS JOIN sys.all_columns ac2;SELECT sd.SomeDate      ,DATENAME (dw, sd.SomeDate)      ,WeekNo = DATEDIFF (                             ww                            ,DATEDIFF (d, 0, DATEADD (m, DATEDIFF (m, 7, sd.SomeDate), 0)) / 7 * 7                            ,DATEADD (d, 0, sd.SomeDate)                         ) + 1FROM #SomeDates sdORDER BY sd.SomeDate;`

• This reply was modified 6 months, 2 weeks ago by  Phil Parkin.
• This reply was modified 6 months, 2 weeks ago by  Phil Parkin.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• rjaye03 wrote:

SET DATEFIRST 6 ; -- Set first Day of Week to Saturday SELECT [CreateDateTime],DATEPART(WEEK,[CreateDateTime]) - DATEPART(WEEK,CAST(CAST(YEAR([CreateDateTime]) AS VARCHAR(4))+'-' + CAST(MONTH([CreateDateTime]) AS VARCHAR(2)) + '-01' AS DATETIME))+1 AS WeekNo FROM yourtable

Ah, be careful now.  I'm going to recommend that any solution that relies on setting the value of DATEFIRST is flawed because, in the day and age of frequent buyouts, you cannot predict future values of DATEFIRST nor can SET statements be executed within view or functions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Just a caution...

The WEEK (ww) datepart is ALWAYS based on Sunday for DATEDIFF.  MS did that to ensure that the DATEDIFF(wk) functionality will be "Deterministic" so that it can be used in things like PERSISTED computed columns. Here's the reference for that nuance:

https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql#remarks

As a result, I avoid the WEEK (wk) datepart at all times because 1) others may try to use the code for other days of the week (with mods, of course) and that would lead to "Silent Failures" for them and 2) I might forget if my corpuscles have not attained sufficient levels of caffeine. 😀

I also NEVER base "fixed" requirement date calculations (all weeks start on a given day of the week) on the use of DATEFIRST because, in this worldwide economy, that can change at the drop of a hat... any hat. 😀  Yep, that can be compensated for but there's that corpuscles and caffeine thing again.  With that I'll say that I'd reject the currently accepted answer (at the time of this post, and has since been changed) during a code review.  So would my corpuscles, properly caffeinated or not. 😀

I have also taken to trying to make sure that dates prior to the SQL Server epoch of 1900-01-01 can be handled, just in case someone needs to handle earlier dates.

I've not checked the results for the currently accepted answer  (at the time of this post, and has since been changed) because it would never see use if I had my say.  Phil's code comes mighty close but it seems to have an issue with months where the first day of the month is a Sunday as seen in the following output from his code.

With all that in mind, here's a function that takes all of that into account with the only requirement that the given date must be greater than 0001-01-07.  Simple usage examples and other documentation are where they usually can't get lost... in the code. 😉

` CREATE FUNCTION dbo.WeekOfMonthSun/********************************************************************************************************************** Purpose: Return the Week Number (WeekOfMonth) for the month of the given date.----------------------------------------------------------------------------------------------------------------------- Usage Examples:--===== Single Date VariableDECLARE @Date DATE = '2023-09-02'; SELECT * FROM dbo.WeekOfMonthSun(@Date);--===== Table (sys.objects, in this case) containing date or dates and times SELECT  so.create_date        ,DayOfWeek = DATENAME(dw,so.create_date) --Not required. Here for testing.        ,wom.WeekOfMonth   FROM sys.objects so  CROSS APPLY dbo.WeekOfMonthSun(so.create_date) wom  ORDER BY so.create_date --Not required. Here for testing.;----------------------------------------------------------------------------------------------------------------------- Required Definiton of Week Number from the Req: I would define first week as any days of the month that land on the first week of the month. i.e. if September 1 lands on Friday, then September 1 and 2 would be week 1 and week 2 starts on Sunday, September 3. Sunday being the beginning of a week and Saturday the end of a week.----------------------------------------------------------------------------------------------------------------------- Programmer Notes: 1. The code calculates the number of weeks between a known Sunday and the given date.    It then subtracts the number of weeks between a known Sunday and the 1st of the month for the given date.    Since that would result in a "0" based answer, 1 is added to that difference to make a "1" based answer.----------------------------------------------------------------------------------------------------------------------- Revision History: Rev 00 - 29 Sep 2023 - Jeff Moden                      - Initial creation and unit test.                      - Req: https://www.sqlservercentral.com/forums/topic/get-week-number-of-month#post-4297360**********************************************************************************************************************/--===== Function Parameter(s)        (@Date DATE)RETURNS TABLE WITH SCHEMABINDING AS RETURN  SELECT WeekOfMonth = (DATEDIFF(dd,'00010107',@Date)/7) --Weeks from earliest possible Sunday to @Date                    - (DATEDIFF(dd,'00010107',DATEADD(dd,1-DAY(@Date),@Date))/7) --Weeks from earliest Sunday to FoM.                    + 1 --Convert 0 based to 1 based.;GO`

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Jeff, that's nice code, well done!

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• Thank you for the feedback, Phil.  I figured I'd make it "production" worthy because this isn't the first time we've seen this request.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• GrassHopper wrote:

I would define first week as any days of the month that land on the first week of the month.  i.e. if September 1 lands on Friday, then September 1 and 2 would be week 1 and week 2 starts on Sunday, September 3.  Sunday being the beginning of a week and Saturday the end of a week.

You say that a new week starts on a Sunday, but in your sample results, you are expecting 1 to be returned for 2023-04-02 15:12:30. Is this a mistake?

Here is some sample code which generates a range of dates (thank you, Jeff) and then calculates week number for them.

--Edit: don't use this code – it works in most cases, but not all. See Jeff's solution below.

`DROP TABLE IF EXISTS #SomeDates;DECLARE @StartDate DATETIME       ,@EndDate DATETIME       ,@Days INT;SELECT @StartDate = '20230301'      ,@EndDate = '20230501'      ,@Days = DATEDIFF (dd, @StartDate, @EndDate);CREATE TABLE #SomeDates(    SomeDate DATE NOT NULL);INSERT #SomeDates(    SomeDate)SELECT TOP(@Days)       TheDate = DATEADD (dd, ROW_NUMBER () OVER (ORDER BY(SELECT NULL)) - 1, @StartDate)FROM sys.all_columns ac1    CROSS JOIN sys.all_columns ac2;SELECT sd.SomeDate      ,DATENAME (dw, sd.SomeDate)      ,WeekNo = DATEDIFF (                             ww                            ,DATEDIFF (d, 0, DATEADD (m, DATEDIFF (m, 7, sd.SomeDate), 0)) / 7 * 7                            ,DATEADD (d, 0, sd.SomeDate)                         ) + 1FROM #SomeDates sdORDER BY sd.SomeDate;`

[/quote]

Phil Parkin wrote:

GrassHopper wrote:

I would define first week as any days of the month that land on the first week of the month.  i.e. if September 1 lands on Friday, then September 1 and 2 would be week 1 and week 2 starts on Sunday, September 3.  Sunday being the beginning of a week and Saturday the end of a week.

You say that a new week starts on a Sunday, but in your sample results, you are expecting 1 to be returned for 2023-04-02 15:12:30. Is this a mistake?

Here is some sample code which generates a range of dates (thank you, Jeff) and then calculates week number for them.

--Edit: don't use this code – it works in most cases, but not all. See Jeff's solution below.

Phil, yes that was my mistake, it should be week 2 for my example.

• This reply was modified 6 months, 2 weeks ago by  GrassHopper.
• Jeff, thanks for taking the time for that educated explanation.  I learned a lot from it.  I had to look up "corpuscles", for a moment I thought it was a new drink at starbucks.

Thank you all for taking the time and giving me your take on how to tackle this issue.  Much appreciated!

Alex

• Sorry, duplicated post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Sorry, duplicated post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• GrassHopper wrote:

Jeff, thanks for taking the time for that educated explanation.  I learned a lot from it.  I had to look up "corpuscles", for a moment I thought it was a new drink at starbucks.

Thank you all for taking the time and giving me your take on how to tackle this issue.  Much appreciated!

Alex

Thanks for the feedback, Alex.  I appreciate it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• GrassHopper wrote:

I had to look up "corpuscles", for a moment I thought it was a new drink at starbucks.

Thank you all for taking the time and giving me your take on how to tackle this issue.  Much appreciated!

Alex

And thank you for making me laugh with the Starbucks comment 🙂

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 15 posts - 1 through 15 (of 21 total)