Get date for iteration of a DOW

  • Beedle

    SSC Enthusiast

    Points: 149

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

  • Jeff Moden

    SSC Guru

    Points: 995127

    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 DOW
    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
    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Beedle

    SSC Enthusiast

    Points: 149

    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 Enthusiast

    Points: 149

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

  • Beedle

    SSC Enthusiast

    Points: 149

    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 DATETIME
    AS
    /*
    Script Date: 08/11/2009
    Author: Sean Senneka
    Purpose: 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

    Points: 995127

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • jonathan.crawford

    SSCertifiable

    Points: 6366

    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 sure
    DECLARE
     @Year SMALLINT = 2018
    , @Month TINYINT = 2 -- 1-12
    , @DOW TINYINT = 0 -- Sunday = 0, Monday = 1, ect...
    , @Itr TINYINT = 1 -- iteration of specified DOW
    BEGIN
     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

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply