Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save jingyang-li/2df4a0a5151bc1289f6857613a821053 to your computer and use it in GitHub Desktop.
Save jingyang-li/2df4a0a5151bc1289f6857613a821053 to your computer and use it in GitHub Desktop.
A UDF from Robyn Page’s SQL Server DATE/TIME Workbench
IF OBJECT_ID (N'NthDayOfWeekOfMonth') IS NOT NULL
DROP FUNCTION NthDayOfWeekOfMonth
GO
CREATE FUNCTION 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), -- one of Mon, Tue, Wed, Thu, Fri, Sat, Sun
@Nth INT) --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
--https://www.red-gate.com/simple-talk/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment