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 int
@ordinal int,
@loopdate datetime

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

return @ordinal

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.

No comments: