How to get day's week cont of current date in sql.

Question

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 27-Jun-12 22:02pm

rakesh@bbspl.com

Add a Solution

3 solutions

Solution 3

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

Posted 30-Oct-12 1:16am

killing the bug softly...

Solution 2

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... :)

Posted 27-Jun-12 22:24pm

JakirBB

Updated 27-Jun-12 22:29pm

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+ :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mario Majčica · Accepted Answer · 2012-06-27T22:14:00

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