2 Replies Latest reply on Jul 13, 2018 8:06 AM by Fergal Dalton

    Finding the second sunday of a month - Daytime Saving

    Richard Moreau

      Hi all,

       

      I have an issue that I can't figure out the solution.

       

      The goal I the get the date of the second Sunday of a month. I was able to figure this line of code that does the trick:

       

           DATETRUNC('week', DATEADD('week',2,#3-1-2017#), 'sunday') = 3-12-2017 This works

       

      However, if the month starts on a Sunday, I get one week to many.

       

           DATETRUNC('week', DATEADD('week',2,#3-1-2020#), 'sunday') = 3-15-2020 This wrong. It should be the 8th.

       

      Is there a way to deal with this without having to include condition to check if the month starts with a Sunday then.

       

      Let me know,

       

      Thanks,

        • 1. Re: Finding the second sunday of a month
          Richard Moreau

          Hi me again,

           

          As I mentioned in my previous discussion, This how it looks with the bunch of conditions. It makes no sense. even if it works.

           

          IF
              DATENAME('weekday',MAKEDATE(DATEPART('year',[Dates]),3,1),'sunday') = 'Sunday'
              AND
              DATENAME('weekday',MAKEDATE(DATEPART('year',[Dates]),11,1),'sunday') = 'Sunday'
          THEN
              IF 
                  [Dates] >= DATETRUNC('week',DATEADD('week',1,MAKEDATE(DATEPART('year',[Dates]),3,1)),'sunday')
                  AND 
                  [Dates] < DATETRUNC('week',MAKEDATE(DATEPART('year',[Dates]),11,1),'sunday')
                  THEN -5
                  ELSE -4
              END

          ELSEIF
              DATENAME('weekday', MAKEDATE(DATEPART('year',[Dates]),3,1),'sunday') = 'Sunday'
              AND
              DATENAME('weekday',MAKEDATE(DATEPART('year',[Dates]),11,1),'sunday') != 'Sunday'
          THEN
              IF 
                  [Dates] >= DATETRUNC('week',DATEADD('week',1,MAKEDATE(DATEPART('year',[Dates]),3,1)),'sunday')
                  AND 
                  [Dates] < DATETRUNC('week',DATEADD('week',1,MAKEDATE(DATEPART('year',[Dates]),11,1)),'sunday')
                  THEN -5
                  ELSE -4
              END
          ELSEIF
              DATENAME('weekday',MAKEDATE(DATEPART('year',[Dates]),3,1),'sunday') != 'Sunday'
              AND
              DATENAME('weekday',MAKEDATE(DATEPART('year',[Dates]),11,1),'sunday') = 'Sunday'
          THEN
              IF 
                  [Dates] >= DATETRUNC('week',DATEADD('week',2,MAKEDATE(DATEPART('year',[Dates]),3,1)),'sunday')
                  AND 
                  [Dates] < DATETRUNC('week',MAKEDATE(DATEPART('year',[Dates]),11,1),'sunday')
                  THEN -5
                  ELSE -4
              END
          ELSE
              IF 
                  [Dates] >= DATETRUNC('week',DATEADD('week',2,MAKEDATE(DATEPART('year',[Dates]),3,1)),'sunday')
                  AND 
                  [Dates] < DATETRUNC('week',DATEADD('week',1,MAKEDATE(DATEPART('year',[Dates]),11,1)),'sunday')

                  THEN -5
                  ELSE -4
              END

          END

           

           

          If someone has a better way of dealing with this, please let me know.

           

          Thanks,

          • 2. Re: Finding the second sunday of a month - Daytime Saving
            Fergal Dalton

            I had this problem for timezone/DSt calculations. Try this (with Sunday as start-of-week):

            datetrunc('week',dateadd('day',13,DATETRUNC('month',[DATE_VARIABLE])))