Created
July 27, 2020 14:54
-
-
Save jingyang-li/2df4a0a5151bc1289f6857613a821053 to your computer and use it in GitHub Desktop.
A UDF from Robyn Page’s SQL Server DATE/TIME Workbench
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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