Finding the 3rd Tuesday 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

SQL Server 2005 Forums

Transact-SQL (2005)

Finding the 3rd Tuesday of the month

Author

Topic

billsox
Yak Posting Veteran

74 Posts

Posted - 2008-05-27 : 18:41:29

Hi all -- I'm trying to create a function that can return the date in a particular month on which the 3rd Tuesday falls. (Or the 2nd Thursday, or the 5th Monday, etc.) I know that my weeks always start on Sundays (and end on Saturdays) but I can't seem to figure out how to accomplish this. I've searched these forums and found threads that talk about how to build a calendar-type table but it is not clear, at least to me, how to use such calendar to find the Xth weekday of the month. Any ideas? Thanks,

Bill

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-27 : 21:00:09

Well here is one way to do it although I'm not certain you will be able to compile this as a UDF. I couldn't try it because (here at home) I only have 2000. So I couldn't even try the code outside the function but unless I have a typo it should work.

>>it is not clear, at least to me, how to use such calendar to find the Xth weekday of the month
This approach is using a table in the master database as a "numbers" table. I'm returning the numbers 0-31 and using that number in a DATEADD function to generate all the dates that could be in your month. Then limiting those days to whatever day of the week you want. Included is a ranking function: ROW_NUMBER() which will allow us to limit the results to the 2nd, 3rd, whatever occurance.


declare @dtStart datetime
	,@dayOfWeek int
	,@dowOccurance int
select @dtStart = '2008-05-01'  --May 2005
	,@dayOfWeek = 3		--Tuesday
	,@dowOccurance = 3	--3rd occurance


select	dt
from	(
	select	dateadd(day, number, @dtStart) dt
		,row_number() over (order by number) rn 
	from	master..spt_values
	where	type = 'p'
	and	number < 32
	and	datepart(weekday, dateadd(day, number, @dtStart)) = @dayOfWeek --only our weekdays of interest
        and     datediff(month, @dtStart, dateadd(day, number, @dtStart)) = 0 --make sure it's the same month
	) d
where	d.rn = @dowOccurance

Be One with the Optimizer
TG

raja_saminathan
Starting Member

12 Posts

Posted - 2008-05-28 : 03:23:55

Hi,

How about this

--===== Create and populate the Tally table on the fly to replace loops
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC

select Top 1 Date from (select dateadd(d, N-3, '20080101') as Date from dbo.Tally) a
where datename(weekday, Date) = 'Tuesday' and Date <= '20080131' and Datename(dd,date)>=15

Regards,
Rajesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources