SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 how to find which week of month in SQL Server?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

biswa_mahapatra
Starting Member

Ukraine
1 Posts

Posted - 07/05/2005 :  13:49:49  Show Profile  Send biswa_mahapatra an AOL message  Reply with Quote
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.

Edited by - biswa_mahapatra on 07/05/2005 14:03:31

sfrigard
Starting Member

5 Posts

Posted - 07/05/2005 :  14:05:30  Show Profile  Reply with Quote
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?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/05/2005 :  14:06:56  Show Profile  Reply with Quote
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
Go to Top of Page

andy8979
Starting Member

India
36 Posts

Posted - 07/06/2005 :  03:54:29  Show Profile  Reply with Quote
Check the datepart functions in the Books online you will get the week number, week day number and the other details in that.

Anuj.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 07/06/2005 :  04:55:57  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Refer this for date functions
http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

hanbingl
Aged Yak Warrior

652 Posts

Posted - 10/24/2008 :  15:14:57  Show Profile  Reply with Quote
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 2008

Calculation: Current Week Number of the year (43) - Week No of First day of the Month (40) + 1

select cast(datename(week,getdate()) as int)- cast( datename(week,dateadd(dd,1-day(getdate()),getdate())) as int)+1

Edited by - hanbingl on 10/24/2008 15:15:21
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/24/2008 :  15:29:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT DATEPART(DAY, GETDATE() - 1) / 7 + 1 AS theWeekWithinMonth



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

hanbingl
Aged Yak Warrior

652 Posts

Posted - 10/24/2008 :  16:04:20  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/24/2008 :  16:05:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It depends on what a "week" is for op, right?



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

hanbingl
Aged Yak Warrior

652 Posts

Posted - 10/24/2008 :  16:15:26  Show Profile  Reply with Quote
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...
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/24/2008 :  17:19:44  Show Profile  Reply with Quote
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
Go to Top of Page

hanbingl
Aged Yak Warrior

652 Posts

Posted - 10/24/2008 :  17:27:09  Show Profile  Reply with Quote
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.
Go to Top of Page

Vincent1
Starting Member

1 Posts

Posted - 01/01/2009 :  10:38:19  Show Profile  Reply with Quote
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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/01/2009 :  12:17:13  Show Profile  Reply with Quote
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
Go to Top of Page

speti43
Starting Member

1 Posts

Posted - 11/23/2010 :  06:14:05  Show Profile  Reply with Quote
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ISOweek] (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
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;

Go to Top of Page

Julaayi
Starting Member

1 Posts

Posted - 08/15/2013 :  12:45:54  Show Profile  Reply with Quote
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 2008

Calculation: Current Week Number of the year (43) - Week No of First day of the Month (40) + 1

select 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000