12,624,887 members (37,054 online)
Rate this:
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 27-Jun-12 23:02pm

Rate this:

## Solution 1

Try this:

```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:

```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 @TodayInWeek = DATEPART(dw, @dt)

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

PRINT @TodaysInMonth```

Cheers
v3
Member 8070578 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+
Rate this:

## Solution 2

Try this.

```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... :)
v3
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.
Member 8070578 28-Jun-12 4:36am

It works fine here.
Member 8070578 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+ :)
Rate this:

## Solution 3

Hi, this is a working copy of my code try this one...

``` 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.....

Top Experts
Last 24hrsThis month
 ppolymorphe 202 Peter Leow 148 John Simmons / outlaw programmer 120 KarstenK 85 OriginalGriff 80
 OriginalGriff 1,007 ppolymorphe 591 John Simmons / outlaw programmer 479 CPallini 308 Richard MacCutchan 277