How to find 3rd Saturday of a 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

SQL Server 2000 Forums

Transact-SQL (2000)

How to find 3rd Saturday of a month

Author

Topic

billsox
Yak Posting Veteran

74 Posts

Posted - 2005-01-31 : 10:35:31

Hi -- Can anyone tell me how to calculate the date of the 3rd Saturday of any given month?

Thanks in advance,

Bill

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-01-31 : 12:00:08

try this solution

http://www.novicksoftware.com/UDFofWeek/Vol2/T-SQL-UDF-Vol-2-Num-14-udf_DT_NthDayInMon.htm

uses a UDF to find what you are looking for.

-ec

TimS
Posting Yak Master

198 Posts

Posted - 2005-01-31 : 12:05:52

DECLARE
@date datetime
SET DATEFIRST 1 -- should work for any DATEFIRST setting
SET @date = '2005-01-15' -- day 15 of month

SELECT DATEADD ( dd , (7 - ((@@DATEFIRST + DATEPART ( dw , @date )) % 7))%7 , @date )

Edit: This needs tested, I tested it for 2005 using DATEFIRST = 6 or 7

Tim S

dsdeming

479 Posts

Posted - 2005-01-31 : 15:47:20

Here's another way to do it. It relies on a numbers table called Sequence with a single integer column called Counter which holds values from 1 to 8000. I use this table all the time for problems such as this.

DECLARE @dtFirst datetime,
@vcDay varchar( 9 ),
@tiOccurence tinyint

SET @dtFirst = '11/1/2005'
SET @vcDay = 'Thursday'
SET @tiOccurence = 4

SELECT a.DayOfMonth
FROM (
SELECT DayOfMonth = DATEADD( dd, Counter - 1, @dtFirst ), -- -1 because Counter starts at 1
WeekNumber = CASE
WHEN DATEPART( dd, DATEADD( dd, Counter - 1, @dtFirst )) BETWEEN 1 AND 7 THEN 1
WHEN DATEPART( dd, DATEADD( dd, Counter - 1, @dtFirst )) BETWEEN 8 AND 14 THEN 2
WHEN DATEPART( dd, DATEADD( dd, Counter - 1, @dtFirst )) BETWEEN 15 AND 21 THEN 3
WHEN DATEPART( dd, DATEADD( dd, Counter - 1, @dtFirst )) BETWEEN 22 AND 28 THEN 4
ELSE 5
END
FROM Sequence
WHERE DATEADD( dd, Counter - 1, @dtFirst ) < DATEADD( mm, 1, @dtFirst ) -- restrict to one month
AND DATENAME( dw, DATEADD( dd, Counter - 1, @dtFirst )) = @vcDay -- restrict to dw = @vcDay
AND Counter < 32 -- force seek instead of scan
) a
WHERE a.WeekNumber = @tiOccurence

Dennis

BammBamm
Starting Member

9 Posts

Posted - 2005-02-01 : 21:48:57

TimS' approach is sound; I came across the same approach, too. And it works fine.

Here's Mine...

Select GetDate() As DateToConvert,
15 + (7 - DatePart(weekday,DateAdd(dd,1-Day(GetDate()),GetDate()))) As ThirdSatOfMonthDay,
DateAdd(dd,15 + (7 - DatePart(weekday,DateAdd(dd,1-Day(GetDate()),GetDate()))),DateAdd(dd,-Day(GetDate()),GetDate())) As NewDate

Basically, you could substitute a variable (like TimS) or a date column/field for my instances of "GetDate()" to make this work.

How it works...

1) Calculate 3rd Saturday of Month
Fact; the lowest possible day of month for the 3rd Saturday is 15 (i.e.: if the month begins on a Saturday)

Therefore, the 3rd Saturday must be 15 + any number of days from 0-6, depending on what day the month started on.
- I use DateAdd to figure the first of the month by subtracting the days from the field/value (giving me the last day of the previous month) and adding 1 day (to get the first of the month).
- Take the numerical value for the day of week, a value from 1 (Sunday) to 7 (Saturday) and subtract it from 7 (possible starting positions days in week).
- This give me the "day of the month.
- Example is the calculated field "ThirdSatOfMonthDay" in my query

2) Return Date for 3rd Saturday in Month
- use DateAdd to add days to the calculated last day of the previous month, notice the nuance (exclusion of 1) on "DateAdd(dd,1-Day..." vs DateAdd(dd,-Day..." in the number of days vs the starting date to add to.
- Example is the calculated field "NewDate" in my query

Options... If you "know" or "control" the variable/field/column being passed in IS the first of the month, you could drop a lot of date manipulation to look like this (I've hard coded 1/1/2005 as an example):

Select GetDate() As DateToConvert,
15 + (7 - DatePart(weekday,'2005-01-01')) As ThirdSatOfMonthDay,
DateAdd(dd,15 + (7 - DatePart(weekday,'2005-01-01')),DateAdd(dd,-1,'2005-01-01')) As NewDate

Hope this helps!

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2005-02-02 : 02:56:04

declare @dt datetime,@wkno int
set @dt ='2003-01-4'
set @wkno = 7 - datepart(dw,dateadd(d,1 -day(@dt),@dt))
select dateadd(d,14 + @wkno + 1 -day(@dt),@dt)

Enjoy working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources