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: sql
3 Comments:
This was perfect. Just what I was looking for.
Thank you - it saved me a whole bunch of time and heartache.
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
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