Technical Article

Get date for iteration of a DOW

,

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
  @YearSMALLINT= 2018
, @MonthTINYINT= 11 -- 1-12
, @DOW TINYINT= 4 -- Sunday = 0, Monday = 1, ect...
, @ItrTINYINT= 4 -- iteration of specified DOW
)
RETURNS DATE
AS
BEGIN
DECLARE@1stDATE = CONVERT(VARCHAR(2),@Month)+'/1/' + CONVERT(VARCHAR(4),@Year);
DECLARE@RtrnDtDATE = 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

Rate

1 (1)

Share

Share

Rate

1 (1)