Here's a stored procedure that returns the calendar "day of month number", given the year, month, and occurance (1st,2nd,...) and the day of week in question (sunday,monday,...).
I used this for another procedure that determines if daylight saving time is in effect on a given day. see the notes regarding validating input for other use cases, but it should be quite useful.
CREATE PROCEDURE sDayOfMonth
(
@DayOfMonth int OUTPUT,
@year int,
@month int,
@weekofmonth int,
@dayofweek int
)
/*
***********************************************************
*** SP Name: sDayOfMonth
*** Date Created: 02/12/2004
***********************************************************
***
*** Description:
***
*** This procedure will return the calenar day, given the year, month, occurance (weekofmonth = 1st,2nd...) and a day (dayofweek) in question (sunday,monday...).
***
*** Valid weekofmonth values are from 1 to 5. A fifth week (and higher) may return more days than in the actual calenday month, in which case -- is invalid.
***
*** Careful when checking the fifth week in Feburary since the length of the month varies. I don't need that here, so i'm not going to fuss with that. you could easily modify this procedure to validate input.
***
*** day of week is zero based, starting with sunday, e.g. sunday = 0, monday = 1, ... , saturday = 6
***
***
***********************************************************
*/
AS
declare @day int
set @day = 1 -- DATEPART( dd, @today) -- fixed to first day of month for this formula
declare @y int
declare @m int
declare @d int
declare @a int
-- variables for our formula
set @a = (14 - @month) / 12
set @y = @year - @a
set @m = @month + (12* @a) - 2
-- formula to calculate day
set @d = (@day + @y + @y/4 - @y/100 + @y/400 + (31*@m)/12) % 7
declare @retval int
if (( @dayofweek - @d) % 7) > 0
begin
set @retval = ( 7 * @weekofmonth ) - 6 + ( ( @dayofweek - @d) %7 )
end
if (( @dayofweek - @d) % 7) < 0
begin
set @retval = ( 7 * @weekofmonth ) - 6 + ( 7- ABS( ( @d - @dayofweek )% 7 ) )
end
if (( @dayofweek - @d) % 7) = 0
begin
set @retval = ( 7 * @weekofmonth ) - 6
end
select @DayOfMonth = @retval
return