Sunday, September 18, 2005

TSQL date teaser. Bonus Question

Given a date, how do you tell which week in the month is that date?
EG : Given 1-Feb-2005, it is the 1st week in February.
Given 17-Feb-2005, it is the 3rd week in February.


declare @startOfMonth datetime
declare @myDate datetime
declare @weekOfYearForStartOfMonth int
declare @weekInMonth int

select @myDate = '17-feb-2005'
select @startOfMonth = dateadd(d, (day(@myDate)-1)* -1, @myDate)
select @weekOfYearForStartOfMonth = datepart(ww, @startOfMonth)
select datepart(ww, @myDate) - @weekOfYearForStartOfMonth + 1

Labels:

3 Comments:

At 9:56 PM, Anonymous Anonymous said...

This was perfect. Just what I was looking for.
Thank you - it saved me a whole bunch of time and heartache.

 
At 11:53 PM, Anonymous Anonymous said...

or
declare @myDate datetime
declare @theDay int
declare @d int
select @myDate = '29-feb-2004'
select @theDay=DATEPART(d,@myDate)
if @theDay%7=0
select @d=0
else
select @d=1
select floor(@theDay/7)+@d

 
At 9:20 AM, Blogger Jonathan Yong said...

My calculation return different result than yours for certain dates.

Try the following dates:
5-aug-2008
My Calculation : 2
Your Calculation : 1

31-aug-2008
My Calculation : 6
Your Calculation : 5


7-sep-2008
My Calculation : 2
Your Calculation : 1


7-nov-2008
My Calculation : 2
Your Calculation : 1

30-nov-2008
My Calculation : 6
Your Calculation : 5

28-dec-2008
My Calculation : 5
Your Calculation : 4


However in different industry, they could have a slightly different way to calculate week number. So I think both our calculation does not represent the definite and only way to calculate week number.

 

Post a Comment

<< Home