Click here to Skip to main content
12,624,887 members (37,054 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL
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: bad
 
good
Please Sign up or sign in to vote.

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 @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
  Permalink  
v3
Comments
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: bad
 
good
Please Sign up or sign in to vote.

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... :)
  Permalink  
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.
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: bad
 
good
Please Sign up or sign in to vote.

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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.161128.1 | Last Updated 30 Oct 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100