[X]

T-SQL - How to get week of the month of given date

Microsoft has lots of in build function in T-SQL for developers. These functions come very handy in day to day t-sql developer requirement.

Today I came across a need of calculating the "Week of Current/Given Date Month". After going through the existing functions of t-sql, I was able to figure out a way to find out the week of given date. Below is the mathematical formula to calculate this:

Week of Current Month = Week of Year - Week of First Day of Current Month + 1

First Date of Current Month = Given Date - Day of Given Date + 1

 


declare @dt datetime
/*

Week of Current Month = Week of Year - Week of First Day of Current Month + 1
First Date of Current Month = Given Date - Day of Given Date + 1

*/
set @dt = GETDATE() 

select @dt as GivenDate, 
dateadd(dd, - day(@dt) + 1, @dt) as FirstDateOfGivenDate,
datepart(wk, @dt) as WeekOfYearOfGivenDate,
datepart(wk, dateadd(dd, - day(@dt) + 1, @dt)) as WeekOfFirstDateOfGivenDate, 
(datepart(wk, @dt) - datepart(wk, dateadd(dd, - day(@dt) + 1, @dt)) + 1) WeekOfMonthOfGivenDate

set @dt = '12/30/2012'
select @dt as GivenDate, 
dateadd(dd, - day(@dt) + 1, @dt) as FirstDateOfGivenDate,
datepart(wk, @dt) as WeekOfYearOfGivenDate,
datepart(wk, dateadd(dd, - day(@dt) + 1, @dt)) as WeekOfFirstDateOfGivenDate, 
(datepart(wk, @dt) - datepart(wk, dateadd(dd, - day(@dt) + 1, @dt)) + 1) WeekOfMonthOfGivenDate

 

There might be some other to get the same thing done. You can google and check if above solution does not fill your need.

Thanks for going through this code and spending your valuable time.

blog comments powered by Disqus

Posts By Month