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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
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)
) + 1
FROM #SomeDates sd
ORDER BY sd.SomeDate;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - 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.
Helpful Links:
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 Variable
DECLARE @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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, that's nice code, well done!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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.
Helpful Links:
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)
) + 1
FROM #SomeDates sd
ORDER 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.
-
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.
Helpful Links:
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.
Helpful Links:
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.
Helpful Links:
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 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply