SQL Interview Prep

The Problem Statement

Write a Query to provide the date for nth occurrence of Sunday in future from the given date

Example:

Given date = 01-01-2022 (Saturday).
Now, lets consider ‘n’ as occurrence of Sunday in the future from the given date.

Here, we will consider n=3.

Now the 3rd occurrence of Sunday in the future from the ‘01-01-2022’ will be:
(Given Date)+1 day = 02-01-2022 (Sunday)
+ 7 (09-01-2022, Next Sunday)
+ 7 (16-01-2022, Next Sunday)

So the r3d occurrence of Sunday from the Given Date will be 16-01-2022.

Approach to the problem:

Find the weekday using the datepart function.
The DATEPART function considers weekday:
Sunday = 1, Monday = 2, Tuesday = 3, Wednesday = 4, Thursday = 5, Friday = 6, Saturday = 7
For first occurrence of Sunday use DATEADD function, that will add days to (8 - DATEPART(weekday,date) + given_date).
For nth occurrence of Sunday use DATEADD function, that will add weeks to the previous step output.

Solution:

declare @today_date date;
declare @n int;

set @today_date = '2022-01-01';
set @n = 3;

select dateadd(weeks, @n-1, dateadd(days, 8 - datepart(weekday, @today_date), @today_date))

Happy querying! 🚀

Content reference: https://www.youtube.com/watch?v=6XQAokp4UCs&list=PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb&index=4&ab_channel=AnkitBansal

SQL Interview Prep - Day 3

Query that provides date for nth occurrence of Sunday in future from given date

The Problem Statement

Approach to the problem:

Solution: