Week number of the month
-
I am trying to write a query for following:
To find out the week number of the month. Week number corresponds to the first Sunday of the Month.
For example in Month of January 2006, Week 1 starts on 01/01/06 and ends on 01/07/06.Week 2 starts on 01/08/06 and ends on 01/15/06. and so on.
For month of February Week 1 starts on 02/05/06 and ends on 02/11/06. Week 2 starts on 02/12/06 and ends on 02/18/06 and so on.Since Week 1 for february 2006 starts on 02/05/06, any date before this date i.e. from 02/01/06 through 02/04/06 should give the week number as 5 from previous month which runs from 01/29/06 through 02/04/06.
I hope this is not confusing.
Thanks
Kavita
-
this might give you some information.
-
Hi Kavita,
I don't know if the link gave you what you needed (there's a lot there!), but I've had a stab...
--data
declare @t table (i int identity(1, 1), x datetime)
insert @t select top 1000 null from master.dbo.syscolumns a, master.dbo.syscolumns b
update @t set x = dateadd(d, i-1, '20060101')
--calculation
select *, case when Day >= DayOfFirstSundayOfMonth then (Day - DayOfFirstSundayOfMonth) / 7 + 1
else (datepart(d, StartOfMonth-1) - DayOfFirstSundayOfPreviousMonth) / 7 + 1 end as WeekNumber
from
(select *,
datepart(d, x) as Day,
(15 - (datepart(dw, StartOfMonth) + @@datefirst)) % 7 + 1 as DayOfFirstSundayOfMonth,
(15 - (datepart(dw, StartOfPreviousMonth) + @@datefirst)) % 7 + 1 as DayOfFirstSundayOfPreviousMonth
from
(select x, dateadd(mm, datediff(mm, 0, x), 0) as StartOfMonth,
dateadd(mm, datediff(mm, 0, x)-1, 0) as StartOfPreviousMonth from @t) a
) b
I've built it up through a series of derived tables, and have tried to keep things simple rather than brief. You can wrap the logic in a function if you need to
It should work for any 'datefirst' setting.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
although a function like this:
create function dbo.WeekOfTheMonth (@date_in datetime)
returns tinyint
as
begin
declare @Wknumber tinyint
set @Wknumber = (datepart(wk,@date_in) - datepart(wk,dateadd(m,datediff(m,0,@date_in),0))) + 1
return @Wknumber
end
It is in my opinion a lot better to create a calendar table with this value precalculated and perform a simple select on it
Cheers,
* Noel -
Noeld,
I haven't figured a way around it yet but the function needs to return a "5" instead of the "1" it does for 02/01/2006 according to what the user posted...
--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) -
yes. I tried this function it returns 1 instead of 5.
-
Was there something wrong with what I posted? Here, I'll make it easier for you...
I agree with Noeld that it's better to put this data into a static calendar/dates table, though.
--function
create function dbo.WeekNumber (@d datetime)
returns tinyint
as
begin
declare @WeekNumber tinyint
select @WeekNumber = case when Day >= DayOfFirstSundayOfMonth then (Day - DayOfFirstSundayOfMonth) / 7 + 1
else (datepart(d, StartOfMonth-1) - DayOfFirstSundayOfPreviousMonth) / 7 + 1 end
from
(select *,
datepart(d, @d) as Day,
(15 - (datepart(dw, StartOfMonth) + @@datefirst)) % 7 + 1 as DayOfFirstSundayOfMonth,
(15 - (datepart(dw, StartOfPreviousMonth) + @@datefirst)) % 7 + 1 as DayOfFirstSundayOfPreviousMonth
from
(select dateadd(mm, datediff(mm, 0, @d), 0) as StartOfMonth,
dateadd(mm, datediff(mm, 0, @d)-1, 0) as StartOfPreviousMonth) a
) b
return @WeekNumber
end
go
--data
declare @t table (i int identity(1, 1), x datetime)
insert @t select top 1000 null from master.dbo.syscolumns a, master.dbo.syscolumns b
update @t set x = dateadd(d, i-1, '20060101')
--calculation
select x, dbo.WeekNumber(x) as WeekNumber from @t
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Hi Ryan,
Sorry I didn't see your post before. I tried your script and it is working
That's great. Thanks a lot.This is really cool.
Thanks
Kavita
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply