Determining the Ordinal of A Weekday.

So, at work I’m having the fun experience of having to write a job scheduling app, using MSSQL as the back end. One of the things I had to figure out was how to determine of a day was, say, the first Monday of the month or the third Thursday or so forth. I couldn’t find any built in functions that did this, so I had to write one. Behold:

Create Function fn_ReturnOrdinalDay( @TestDate datetime)returns intbegindeclare@ordinal int,@loopdate datetime

set @loopdate = @TestDateset @ordinal = 0while datepart(m, @TestDate) = datepart(m, @loopdate)beginset @ordinal = @ordinal + 1set @testdate = dateadd(d, -7, @testdate)end

return @ordinalend

When called like fn_ReturnOrdinalDay(getdate()) will return 1 - 5 depending on how many times that day has occured. For instance for today (Monday June 18th, 2007) it would return 3 since this is the Third Monday of the Month.