## Get date for iteration of a DOW

 Author Message Beedle SSC-Enthusiastic Group: General Forum Members Points: 111 Visits: 215 Comments posted to this topic are about the item Get date for iteration of a DOW Jeff Moden SSC Guru Group: General Forum Members Points: 895833 Visits: 48232 It's broke...DECLARE @Year SMALLINT = 2017 , @Month TINYINT = 10 -- 1-12 , @DOW TINYINT = 1 -- Sunday = 0, Monday = 1, ect... , @Itr TINYINT = 1 -- iteration of specified DOWBEGINDECLARE @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 @RtrnDtEND --Jeff ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Beedle SSC-Enthusiastic Group: General Forum Members Points: 111 Visits: 215 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); Beedle SSC-Enthusiastic Group: General Forum Members Points: 111 Visits: 215 Wait, still wrong... I'll reply once I fix it Beedle SSC-Enthusiastic Group: General Forum Members Points: 111 Visits: 215 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;` Jeff Moden SSC Guru Group: General Forum Members Points: 895833 Visits: 48232 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Helpful Links:How to post code problemsHow to post performance problemsForum FAQs jonathan.crawford SSCertifiable Group: General Forum Members Points: 5176 Visits: 1465 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. -------------------------------------------------------------------------------------------------------------------------------------Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses