Need to find out which weekday of the month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need to find out which weekday of the month

Author

Topic

davesbrain
Starting Member

2 Posts

Posted - 2011-04-25 : 10:01:07

So given:

Declare @AnyDate date

How do I determine which weekday of the month it is.
I have:

DATEPART(WEEKDAY, @AnyDate)
to determine the day of the week But now how do I determine that this is the 1st

I tried:
(DATEPART(Day, @TestDate)/7) but that was giving me really wonky results.

Finally after I get
(3rd Tuesday) for example I need to set 3rd Tuesday of THIS month.

Any help is greatly appreciated.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-25 : 10:08:58

Not sure who I lifted this from
DECLARE
@theDate DATETIME,
@theWeekday TINYINT,
@theNth SMALLINT

SET @theDate = getdate()
set @theWeekday = 2
SET @theNth = 3

SELECT theDate
FROM (
SELECT DATEADD(DAY, 7 * @theNth - 7 * SIGN(SIGN(@theNth) + 1) +(@theWeekday + 6 - DATEDIFF(DAY, '17530101', DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')) % 7) % 7, DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')) AS theDate
WHERE @theWeekday BETWEEN 1 AND 7
AND @theNth IN (-5, -4, -3, -2, -1, 1, 2, 3, 4, 5)
) AS d

Jim

Everyday I learn something that somebody else already knew

davesbrain
Starting Member

2 Posts

Posted - 2011-04-25 : 16:09:17

Thanks that works for me!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources