Part of this requirement required determining the "how many'th Dayname" in a given month a given date was. ( eg. First Monday, or Third Saturday, or Last Sunday
Herewith some code:
DECLARE @D DATETIME
set @D = '2010-04-23'
SELECT DATENAME ( dw, @d ) as DateNamePart,
CASE ceiling((1.0 + ABS( DATEDIFF( dd, @d, CAST( CAST( YEAR( @d ) AS varchar ) +
RIGHT( '00' + CAST( MONTH( @d ) AS varchar ) ,2 ) + '01' AS datetime )))) / 7)
when 1 then 'First'
when 2 then 'Second'
when 3 then 'Third'
when 4 then 'Fourth'
when 5 then 'Fifth'
else 'None'
END as Occurence
Result:
