March 20, 2009 at 12:57 am
Hi Everybody,
Can anyone help me out to find the week of the month. It query or function should return number of the the week. If the first day of the month is starting on saturday(1st). The the nextday that is 2nd(sunday) should be treated as next week that is week 2.
Who is wise? He that learns from everyone. Who is powerful? He that governs his passions. Who is rich? He that is content. Who is that? Nobody.:)
March 20, 2009 at 2:33 am
Hello,
If you subtracted the result of DatePart(wk) for the first day of the Month , from the DatePart(wk) for the Date you are calculating, and then add one, would that work?
For example:-
Select DatePart(wk , GetDate()) - DatePart(wk , '1 March 2009') + 1
Or more fully:-
Select DatePart(wk , GetDate()) - DatePart(wk , '1 ' + DateName(m, GetDate()) + ' ' + Cast(Year(GetDate()) As Char(4))) + 1
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
March 20, 2009 at 3:40 am
[font="Verdana"]Hi Naveen,
This is one more solution to your query...
SELECT DATEPART(WK, GETDATE())
- DATEPART(WK, DATEADD(DAY, - CAST(DATENAME(DAY,GETDATE()) AS INT), GETDATE())) AS WEEK_OF_THIS_MONTH
Regards,
Ashok S[/font]
March 20, 2009 at 5:20 am
Thanks John and Ashok
Who is wise? He that learns from everyone. Who is powerful? He that governs his passions. Who is rich? He that is content. Who is that? Nobody.:)
April 2, 2009 at 1:43 am
[font="Verdana"]
SELECT DATEPART(WK, GETDATE())
- DATEPART(WK, DATEADD(DAY, - CAST(DATENAME(DAY,GETDATE()) AS INT), GETDATE())) AS WEEK_OF_THIS_MONTH
[/font]
[font="Verdana"]Hi,
The query which I gave here has got a bug. It will fetch you proper result only if the month end falls in weekend. Ex: If you take 2009 Jan and Feb the last day falls in weekend. Hence the query will give proper result till March 31. But if you try to get for the month of Apr, then we will get wrong data. Here is the modified version of the same query,
DECLARE @DATE AS DATETIME
--SET @DATE = GETDATE()
SET @DATE = '04/09/09'
SELECT
CASE WHEN DATEPART(WK, DATEADD(DAY, - CAST(DATENAME(DAY,@DATE) AS INT), @DATE))
= DATEPART(WK, DATEADD(DAY, - (CAST(DATENAME(DAY,@DATE) AS INT) - 1), @DATE))
THEN
DATEPART(WK, @DATE)
- DATEPART(WK, DATEADD(DAY, - CAST(DATENAME(DAY,@DATE) AS INT), @DATE)) + 1
ELSE
DATEPART(WK, @DATE)
- DATEPART(WK, DATEADD(DAY, - CAST(DATENAME(DAY,@DATE) AS INT), @DATE))
END
WEEK_OF_THIS_MONTH
This query will work properly as expected.
Sorry for the improper code.
Regards,
Ashok S[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply