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:

<code>Create Function fn_ReturnOrdinalDay( @TestDate datetime) returns int
begin
declare @ordinal int, @loopdate datetime

set @loopdate = @TestDate
set @ordinal = 0

</code>




<code>while datepart(m, @TestDate) = datepart(m, @loopdate)
begin
</code>




<code>    set @ordinal = @ordinal + 1
    set @testdate = dateadd(d, -7, @testdate)
end

return @ordinal
end
</code>

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.