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
	  @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

Rate

1 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

1 (1)

You rated this post out of 5. Change rating