Building a calendar table is always a good design choice in an application. But sometimes, it is faster to calculate the value you want instead of counting the value from a calendar table.
I am going to show you how to write a function that calculated the Nth weekday of any interval. Let’s say you want to get the 5th sunday of the third quarter in 2018, or the 2nd sunday in May 2018, how can you calculate that?
If you want to count the value, you can easy write code like this
This is fine and all, but what if you want to get the 2nd last sunday in 2018? Then you have to use ORDERÂ BY DESC to get the result you want.
So, how can you calculate the date instead? Let me show you how.
Start with creating the actual start date for the wanted weekday. If you want the second sunday, it’s obvious the date cannot be within the first seven days in the interval. Likewise, if you want the last sunday, it must be within the last seven days of the interval. So the trick is to calculate the Nth seven day interval within the wanted interval, no matter if you want the first or last weekday in the wanted interval.
While calculating the seven day period within the interval, you can also optimize the query by checking that you can only use a valid number for the weekday (0 – monday to 6 – sunday) and also that you cannot choose the millionth monday, because that would be an out of bounds date. And just to be sure, we are using the integer representation of the date, because the intermediate result can be later than 9999-12-31 or earlier than 0001-01-01.
When you have calculated the wanted start date, you now have to check to see of the calculated start date is a week day later than the wanted week day. The check is fairly simple beause you take the calculated start date and get the Monday for that date and get the second wanted weekday if the start date week day is later than the wanted weekday.
When you put the things together, you get the following query.
And you also have to check that the @Nth parameter value actually falls within the wanted range. For example, if you want the seventh thursday in may 2018, there is no result.
And now you know that the second sunday in May 2018 is May 13th.