Function to get the next 1st thursday from the given day. Can be easily change to give the next1st any weekday.
If 1st thursday of the current month is passed , it gives the next month's 1st thursday.
Function to get the next 1st thursday from the given day. Can be easily change to give the next1st any weekday.
If 1st thursday of the current month is passed , it gives the next month's 1st thursday.
CREATE FUNCTION dbo.Getnext1stthursday(@currentDate DATETIME) RETURNS DATETIME AS BEGIN DECLARE @1stThurs DATETIME; DECLARE @daysToAdd INT = CASE WHEN ( 5 - Datepart(weekday, Dateadd(DD, 1 - Day(@currentDate), @currentDate)) ) < 0 THEN --Thursday Passed 7 + ( 5 - Datepart(weekday, Dateadd(DD, 1 - Day(@currentDate), @currentDate)) ) ELSE ( 5 - Datepart(weekday, Dateadd(DD, 1 - Day(@currentDate), @currentDate)) ) END SET @1stThurs = CONVERT(DATE, Dateadd(dd, @daysToAdd, Dateadd(DD, 1 - Day(@currentDate), @currentDate))); RETURN CASE WHEN @1stThurs < @currentDate THEN dbo.Getnext1stthursday(Dateadd(DD, 1 - Day(Dateadd(mm, 1, @currentDate)), Dateadd(mm, 1, @currentDate))) ELSE @1stThurs END; END