| Author |
Topic |
|
biswa_mahapatra
Starting Member
1 Post |
 Posted - 2005-07-05 : 13:49:49
|
| 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. |
|
|
sfrigard
Starting Member
5 Posts |
Posted - 2005-07-05 : 14:05:30
|
| What do you define as week one? If Saturday is the last day of a week and the first of the month falls on a Saturday, does that qualify as week one. Some orginizations classify the first week starting with Sunday, others classify the week containing the first Wednesday as the first week. Could you please clarify? |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-07-05 : 14:06:56
|
You will need to provide a precise definition of what you consider a "week of the month".quote:
Originally posted by biswa_mahapatra
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.
CODO ERGO SUM |
|
|
|
andy8979
Starting Member
36 Posts |
Posted - 2005-07-06 : 03:54:29
|
Check the datepart functions in the Books online you will get the week number, week day number and the other details in that.Anuj.  |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-24 : 15:14:57
|
I came across this post... which wasn't clearly answered 3 years ago:he wanted Week Number of the current Month, 2008-10-24 is the 4th week of October in 2008Calculation: Current Week Number of the year (43) - Week No of First day of the Month (40) + 1select cast(datename(week,getdate()) as int)- cast( datename(week,dateadd(dd,1-day(getdate()),getdate())) as int)+1 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-24 : 15:29:30
|
SELECT DATEPART(DAY, GETDATE() - 1) / 7 + 1 AS theWeekWithinMonth
E 12°55'05.63"N 56°04'39.26" |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-24 : 16:04:20
|
quote:
Originally posted by Peso
SELECT DATEPART(DAY, GETDATE() - 1) / 7 + 1 AS theWeekWithinMonth
E 12°55'05.63"N 56°04'39.26"
If 1st day of the week starts on a weekend, it won't work. '2008-08-25' for example is week 5, but your formula shows 4 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-24 : 16:05:51
|
It depends on what a "week" is for op, right?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-24 : 16:15:26
|
| Normally we use week of the month in holidays or events. For example, 3rd week of October is "The Great SQL Week". So, I think it is pretty standard for me to think of calender weeks.But yup, the OP never responded to anyone for 3yrs I guess we'll never find out what OP wants... |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-10-24 : 17:19:44
|
| Do you really think that someone who posted one time 3 years ago, and didn't even bother to respond to requests for a definition of "week of the month" is going to be looking at your response?We see many questions posted like this about week of month, week of year, etc. The problem is that the definition of week varies widely from one organization to next, so it is useless to speculate about the actual definition. In addition, most of the people posting these questions do not seen to understand the definition themselves, are unable to give an understandable explanation, or don’t bother to respond.CODO ERGO SUM |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-24 : 17:27:09
|
| Agreed, I searched this post because I had the same question and want to see if anyone has the solution for me already. I was answering based on my understanding, just another addition to the pool. In case others came across. |
|
|
|
Vincent1
Starting Member
1 Post |
Posted - 2009-01-01 : 10:38:19
|
quote:
Originally posted by hanbingl
Agreed, I searched this post because I had the same question and want to see if anyone has the solution for me already. I was answering based on my understanding, just another addition to the pool. In case others came across.
I for one appreciate the follow-up! I understand there are caveats but your work will save me some tedium in solving my problem. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-01-01 : 12:17:13
|
quote:
Originally posted by Vincent1
quote:
Originally posted by hanbingl
Agreed, I searched this post because I had the same question and want to see if anyone has the solution for me already. I was answering based on my understanding, just another addition to the pool. In case others came across.
I for one appreciate the follow-up! I understand there are caveats but your work will save me some tedium in solving my problem.
If you have a question about how to find the week of the month, it would be better if you started a new topic.The same thing applies to your problem: We would need an exact definition of the week of the month to be able to help. The definition of week of the month varies from one organization to the next, so we need to know your rules to be able to help.CODO ERGO SUM |
|
|
|
speti43
Starting Member
1 Post |
Posted - 2010-11-23 : 06:14:05
|
| SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION [dbo].[ISOweek] (@DATE datetime)RETURNS intWITH EXECUTE AS CALLERASBEGIN DECLARE @WeekOfMonth TINYINT SET @WeekOfMonth = (DAY(@DATE) + (DATEPART(dw, DATEADD (MONTH, DATEDIFF (MONTH, 0, @DATE), 0)) --^-- The day of the week for the first day of month -1) -- # of days to add to make the first week full 7 days -1)/7 + 1 RETURN(@WeekOfMonth)END; |
|
|
|
Julaayi
Starting Member
1 Post |
Posted - 2013-08-15 : 12:45:54
|
quote:
Originally posted by hanbingl
I came across this post... which wasn't clearly answered 3 years ago:he wanted Week Number of the current Month, 2008-10-24 is the 4th week of October in 2008Calculation: Current Week Number of the year (43) - Week No of First day of the Month (40) + 1select cast(datename(week,getdate()) as int)- cast( datename(week,dateadd(dd,1-day(getdate()),getdate())) as int)+1
That works like a charm. Thank you very much |
|
|
|
|
how to find which week of month in SQL Server?