I know this post is old but here's another method for 2008 and up (Comment out any returned columns that you don't need).
--===== 2008 and Up
SELECT Occurance# = (DAY(GETDATE())-1)/7+1
,Occurance = CASE
WHEN MONTH(GETDATE()) = MONTH(DATEADD(dd,7,GETDATE()))
THEN v.OccuranceName
ELSE 'LAST'
END
,DoW = DATENAME(dw,GETDATE())
FROM (VALUES (1,'1st'),(2,'2nd'),(3,'3rd'),(4,'4th'),(5,'5th'))v(Occurance#,OccuranceName)
WHERE v.Occurance# = (DAY(GETDATE())-1)/7+1
;
On my box and dumping the output to variables to remove display durations, it runs on a million dates in about 392ms.
Just as a bit of a sidebar (it's just a habit of mine to check), Phil's good code takes 654ms to do the same thing. I agree that's not much especially considering it's for a million rows but it does run 66% faster. Imagine if everything on your server ran "just" 66% faster. ;)
From reading the comments, I also know this request was for a 2008 instance but figured I'd give it a try using the wiles of 2012. It makes for some very short code, indeed, but EOM is a wee-bit slower than most brute-force methods and so the following took 408ms. The simplicity might be well worth the tiny loss, though.
--===== 2012 and Up
SELECT Occurance# = (DAY(GETDATE())-1)/7+1
,Occurance = IIF(DATEDIFF(dd,GETDATE(),EOMONTH(GETDATE()))<7
,'Last'
,CHOOSE((DAY(GETDATE())-1)/7+1,'1st','2nd','3rd','4th'))
,DoW = DATENAME(dw,GETDATE())
;