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

# Finding the second sunday of a month - Daytime Saving

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

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
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
AND
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
AND
[Dates] < DATETRUNC('week',MAKEDATE(DATEPART('year',[Dates]),11,1),'sunday')
THEN -5
ELSE -4
END
ELSE
IF
AND

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

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