Click here to Skip to main content
15,867,328 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Today is 28 Jun 2012 Thursday.
I need to find out the which number of thursday is this in that particular month.

As 4 in the above case.
How to find the same for any date in SQL Server.
Posted

Try this:

SQL
DECLARE @dt DATETIME, @WeekOfMonth TINYINT
SET @dt = '2007-07-08'
SET @WeekOfMonth = DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dt), 0), @dt) +1
PRINT @WeekOfMonth



Cheers

EDIT:

After better analyzing your question, this should be a proper solution:

SQL
DECLARE @dt DATETIME, @WeekOfMonth TINYINT, @FirstDayOfMonth TINYINT, @TodayInWeek TINYINT, @TodaysInMonth TINYINT
SET @dt = GETDATE()

SET @WeekOfMonth = DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dt), 0), @dt) +1

SET @FirstDayOfMonth = DATEPART(dw,(DATEADD(dd,-(DAY(@dt)-1), @dt)))
SET @TodayInWeek = DATEPART(dw, @dt)

IF @TodayInWeek < @FirstDayOfMonth
    BEGIN
        SET @TodaysInMonth = @WeekOfMonth - 1
    END
ELSE
    BEGIN
        SET @TodaysInMonth = @WeekOfMonth
    END

PRINT @TodaysInMonth


Cheers
 
Share this answer
 
v3
Comments
JakirBB 28-Jun-12 4:29am    
For @dt='2012-06-28' it returns 5
Mario Majčica 28-Jun-12 4:29am    
Try the last edit, it turns 4!
damodara naidu betha 30-Oct-12 9:09am    
My 5+
Try this.

SQL
DECLARE @dt DATETIME, @WeekOfMonth TINYINT, @day int
SET @dt = '2012-06-28'
SET @day = DAY(@dt)-7
SET @WeekOfMonth = (CASE WHEN @day <= 0 THEN 0 ELSE @day/7 END)+1
PRINT @WeekOfMonth


Cheers... :)
 
Share this answer
 
v3
Comments
Mario Majčica 28-Jun-12 4:32am    
This is not even compiling:
Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
JakirBB 28-Jun-12 4:36am    
It works fine here.
JakirBB 28-Jun-12 4:41am    
Try to have some essential understanding. It's not my fault.
change the value of @dt to current date in your code. And tell the result to me.
It's datetime format problem. You should understand.

Again a if a task can be done in a simple way then why should we think of other ways?
Mario Majčica 28-Jun-12 4:50am    
Dude, you are right. My 5 ;)
damodara naidu betha 30-Oct-12 9:08am    
My 5+ :)
Hi, this is a working copy of my code try this one...

SQL
 ALTER Procedure [dbo].[proc_GetProjectDeploymentTimeSheetData] '2012-10-29', '2012-11-29'
@FromDate date,
@ToDate date

As 
Begin
select p.ProjectName + ' ( ' + st.Time +' '+'-'+' '+et.Time +' )' as ProjectDeatils, datename(dw,pts.StartDate) as 'Day'
from 
ProjectTimeSheet pts 
join Projects p on pts.ProjectID=p.ID 
join Timing st on pts.StartTimingId=st.Id
join Timing et on pts.EndTimingId=et.Id
where pts.StartDate >= @FromDate
and pts.StartDate <= @ToDate

order by 
END


Happy coding.....
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900