Let me know if you find that it breaks with anything....(I know it won't be correct if you specify an invalid @Itr, just want to know if the math doesn't work for any dates checked)
2017-12-29
1,074 reads
Let me know if you find that it breaks with anything....(I know it won't be correct if you specify an invalid @Itr, just want to know if the math doesn't work for any dates checked)
CREATE FUNCTION GetDateForDOW ( --DECLARE @Year SMALLINT = 2018 , @Month TINYINT = 11 -- 1-12 , @DOW TINYINT = 4 -- Sunday = 0, Monday = 1, ect... , @Itr TINYINT = 4 -- iteration of specified DOW ) RETURNS DATE AS BEGIN DECLARE @1st DATE = CONVERT(VARCHAR(2),@Month)+'/1/' + CONVERT(VARCHAR(4),@Year); DECLARE @RtrnDt DATE = DATEADD(DAY,((CEILING((CONVERT(DECIMAL(8, 2),17) % DATEPART(dw, @1st)) / 10) * 7) + (@DOW+((@Itr-2)*7))+1) - DATEPART(dw, @1st), @1st); --SELECT @RtrnDt RETURN @RtrnDt; END GO