how to find Nth week in a month for a given date in sql server?
-
Hi All,
Can anybody tell me given a date is which Nth week of the month. And also i need to find no. of weeks in that month.
Help appreciated.
-
SQL Server has a week function, but you may find it rather odd to work with.
Try the following and see what you get.
SELECT DATEPART( week, '01/01/2005')
SELECT DATEPART( week, '01/02/2005')
(non-bulldozer response......)
I wasn't born stupid - I had to study.
-
but that shows week of the year..not for the month...
-
Noel, Thanks a lot !!!
-
Just note that weeknumbers are ambigous on a global scale. Different countries have different rules on when week one starts for a given year, and also in which weeknumber a year ends as a consequence.
Also, (though this may be clear to you in your case) when asking a question like - how many weeks are there in a month? - you also must establish rules of 'what is a week'. Is it a 7-day period? Is it first-last weekday according to your chosen national calendar rules? Must the entire week be within the month? etc etc..
/Kenneth
-
declare @d datetime, @n int
set @d = '2005-12-15' -- to say anything
set @n = datepart(wk,@d) + 1 - datepart(wk,dateadd(d, 1-day(@d),@d ))
select @n -- the nth week...
-
And, not tested but, the number of weeks per month
declare @d datetime, @n int
set @d = '2005-12-15' -- sample date
set @n = datepart(wk,dateadd(d,-1,dateadd(m,1,dateadd(d, 1-day(@d),@d )))) - datepart(wk,dateadd(d, 1-day(@d),@d )) +1
select @n -- the no of weeks in month(@d)...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply