SELECT PhilFactor.dbo.NthDayOfWeekOfMonth ('2017', 'Jun', 'Fri',3)
CREATE FUNCTION dbo.NthDayOfWeekOfMonth (
@TheYear CHAR(4), --the year as four characters (e.g. '2014')
@TheMonth CHAR(3), --in english (Sorry to our EU collegues) e.g. Jun, Jul, Aug
@TheDayOfWeek CHAR(3)='Sun', -- one of Mon, Tue, Wed, Thu, Fri, Sat, Sun
@Nth INT=1) --1 for the first date, 2 for the second occurence, 3 for the third
RETURNS DATETIME
WITH EXECUTE AS CALLER
AS
BEGIN
RETURN DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(DATE,'1 '+@TheMonth+' '+@TheYear,113)), 0)+ (7*@Nth)-1
-(DATEPART (WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(DATE,'1 '+@TheMonth+' '+@TheYear,113)), 0))
+@@DateFirst+(CHARINDEX(@TheDayOfWeek,'FriThuWedTueMonSunSat')-1)/3)%7
END
go
-- in SQL Server 2012 onwards, you can either use either the EXECUTE command or the SELECT command to
--execute a function. The main difference is in the way that parameters with defaults are handled but
--also the schema name is not required in the execute syntax.
DECLARE @ret DateTime
EXEC @ret = NthDayOfWeekOfMonth '2017', 'Jun', 'Fri',3
SELECT @ret AS Third_Friday_In_June_2017
EXEC @ret = NthDayOfWeekOfMonth '2017', 'Jun'
SELECT @ret AS First_Sunday_In_June_2017
--or you can use the conventional syntax.
--Note that you have to give all parameters
SELECT dbo.NthDayOfWeekOfMonth('2017', 'May', DEFAULT, DEFAULT) AS 'Using default',
dbo.NthDayOfWeekOfMonth('2017', 'May', 'Sun', 1) AS 'explicit'