# Get date for iteration of a DOW

• Comments posted to this topic are about the item Get date for iteration of a DOW

• It's broke...

DECLARE
@Year

SMALLINT= 2017
, @MonthTINYINT= 10 -- 1-12
, @DOW TINYINT= 1 -- Sunday = 0, Monday = 1, ect...
, @ItrTINYINT= 1 -- iteration of specified DOW
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
END

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• you're right - my bad, the "DECLARE @RtrnDt" should be:
DECLARE @RtrnDt DATE = DATEADD(DAY,((CEILING((CONVERT(DECIMAL(8, 2),17) % DATEPART(dw, @1st)) / 10) * 7) + ((@Itr*7)+@DOW+1)-7) - DATEPART(dw, @1st), @1st);

• Wait, still wrong...  I'll reply once I fix it

• Full disclosure, I attempted to convert from this function I wrote 10 years ago, but am getting the math wrong now.  - I'm pretty sure each part in this script works, I'm just not applying the new variables correctly.
`CREATE FUNCTION [dbo].[udfConvertFromGMT](  @InputDate DATETIME)RETURNS DATETIMEAS/*Script Date: 08/11/2009Author: Sean SennekaPurpose: Converts from GMT to CST (or CDT if date is during daylight savings time)*/--DECLARE @InputDate datetime--Select @InputDate = '10/4/1985 08:00:00'BEGIN  DECLARE @converted_date DATETIME;  DECLARE @DST_Start DATETIME;  DECLARE @DST_End DATETIME;  DECLARE @GMT_Offset_Destination INT;  -- Get start and end dates for daylight savingstime for the year in question  SELECT @DST_Start = CASE          WHEN DATEPART(YEAR, @InputDate) >= 2007 THEN           '3/1/' + CONVERT(VARCHAR, DATEPART(YEAR, @InputDate))          WHEN DATEPART(YEAR, @InputDate)           BETWEEN 1986 AND 2006 THEN           '4/1/' + CONVERT(VARCHAR, DATEPART(YEAR, @InputDate))          ELSE -- Year prior to 1986`

`           '4/30/' + CONVERT(VARCHAR, DATEPART(YEAR, @InputDate))        END,    @DST_End = CASE         WHEN DATEPART(YEAR, @InputDate) >= 2007 THEN          '11/1/' + CONVERT(VARCHAR, DATEPART(YEAR, @InputDate))         ELSE          '10/31/' + CONVERT(VARCHAR, DATEPART(YEAR, @InputDate))        END;`

`  SELECT @DST_Start   =   --2007 till present get second Sunday of March, 1986-2006 get first Sunday of April, prior to 1986 get last sunday in April`

`   CASE    WHEN DATEPART(YEAR, @InputDate) >= 2007 THEN      DATEADD(         HOUR,         8,         DATEADD(             DAY,             ((CEILING((CAST(17 AS DECIMAL(8, 2)) % DATEPART(dw, @DST_Start)) / 10) * 7) + 8)             - DATEPART(dw, @DST_Start),             @DST_Start            )        )    WHEN DATEPART(YEAR, @InputDate)      BETWEEN 1986 AND 2006 THEN      DATEADD(         HOUR,         8,         DATEADD(             DAY,             ((CEILING((CAST(17 AS DECIMAL(8, 2)) % DATEPART(dw, @DST_Start)) / 10) * 7) + 1)             - DATEPART(dw, @DST_Start),             @DST_Start            )        )    ELSE -- Year prior to 1986`

`      DATEADD(HOUR, 8, DATEADD(DAY, 0 - (DATEPART(dw, @DST_Start) - 1), @DST_Start))   END,`

`    --2007 till present get first Sunday of November, else get last Sunday of October`

`    @DST_End     = CASE       WHEN DATEPART(YEAR, @InputDate) >= 2007 THEN         DATEADD(            SECOND,            -1,            DATEADD(                HOUR,                8,                DATEADD(                    DAY,                    ((CEILING((CAST(17 AS DECIMAL(8, 2)) % DATEPART(dw, @DST_End))                       / 10                       ) * 7                    ) + 1                    ) - DATEPART(dw, @DST_End),                    @DST_End                  )               )           )       ELSE         DATEADD(SECOND, -1, DATEADD(HOUR, 8, DATEADD(DAY, 0 - (DATEPART(dw, @DST_End) - 1), @DST_End)))      END,    @GMT_Offset_Destination = -360;`

`  RETURN CASE     WHEN @InputDate       BETWEEN @DST_Start AND @DST_End THEN       DATEADD(MINUTE, @GMT_Offset_Destination + 60, @InputDate)     ELSE       DATEADD(MINUTE, @GMT_Offset_Destination, @InputDate)    END;`

`END;`

• Take your time.  You can't think if you're in a rush.   Put it up for a couple of days and come back to it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• I am going to offer an alternative for you, that I think is a lot more readable/understandable.

First, note the DATEFROMPARTS() function, which you would use since you're just feeding in a year and month, that's new as of 2012. Secondly, the idea is that you only really need to find the first Monday (or whatever day we're looking for), if you want the next one then just add a week. Third, since you're using a zero-based array for @DOW, the DATEPART(dw,@1st) function will not equal zero ever, so we have to adjust @DOW when comparing.
`SET DATEFIRST 7 --week starts with Sunday, default for English, but set to make sureDECLARE @Year SMALLINT = 2018, @Month TINYINT = 2 -- 1-12, @DOW TINYINT = 0 -- Sunday = 0, Monday = 1, ect..., @Itr TINYINT = 1 -- iteration of specified DOWBEGIN DECLARE @1st DATE = DATEFROMPARTS(@Year,@Month,1) DECLARE @RtrnDt DATE  --if our day of the week for the 1st is the same as we are looking for, then return it  --we can find the next same day of the week by adding a week to that day IF DATEPART(dw,@1st) = (@DOW + 1) --adjusting to accomodate zero-based array BEGIN  SET @RtrnDt = DATEADD(ww,@Itr-1,@1st); END --if our @DOW is later in the week, offset from the 1st by the number of days larger and add in our week iterator ELSE IF DATEPART(dw,@1st) < (@DOW + 1) BEGIN  SET @RtrnDt = DATEADD(ww,@Itr-1,DATEADD(dd,(@DOW+1)-DATEPART(dw,@1st),@1st)); END ELSE -- the @DOW is earlier in the week, subtract the value from the 1st and add in week iterator BEGIN  SET @RtrnDt = DATEADD(ww,@Itr-1,DATEADD(dd,DATEPART(dw,@1st)-(@DOW),@1st)); END SELECT @RtrnDt;END`

Hope that helps if you can't get your math working.

-------------------------------------------------------------------------------------------------------------------------------------