Click here to Skip to main content
Rate this: bad
good
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 22: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 at 28-Jun-12 4:29am
   
For @dt='2012-06-28' it returns 5
Mario Majčica at 28-Jun-12 4:29am
   
Try the last edit, it turns 4!
damodara naidu betha at 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... Smile | :)
  Permalink  
v3
Comments
Mario Majčica at 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 at 28-Jun-12 4:36am
   
It works fine here.
Member 8070578 at 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 at 28-Jun-12 4:50am
   
Dude, you are right. My 5 ;)
damodara naidu betha at 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
0 Sergey Alexandrovich Kryukov 560
1 OriginalGriff 325
2 CPallini 280
3 ChauhanAjay 149
4 BillWoodruff 130
0 OriginalGriff 6,474
1 Sergey Alexandrovich Kryukov 6,190
2 CPallini 5,180
3 George Jonsson 3,574
4 Gihan Liyanage 2,522


Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 30 Oct 2012
Copyright © CodeProject, 1999-2014
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