I am trying to find a specific days within specific months for DST settings. I found code from another post.
SELECT
DATEADD(day,DATEDIFF(day,'19000107',DATEADD(month,DATEDIFF(MONTH,0,GETDATE()/*YourValuehere*/),30))/7*7,'19000107')
I also found this in another post which could be used a month ahead.
SELECT DATEADD(day,-1 - (DATEPART(weekday, @StartOfMarch) + @@DATEFIRST - 2) % 7,
@StartOfMarch
) AS LastSunday
I am not great with SQL so I am looking for a dummy explanation of what is happening here, and why this code works. From what I gather, Jan 07 1900 is significant in here as is the various built in date time functions. A simple play-by-play breakdown would help me understand what all is happening.
This is for the SQL Server 2016.
Well, since 2012 version there is a built in function to get the end of the month for any date / datetime value called EOMONTH.
Using this function with DATEPART and DATEADD is quite a simple way to get the date of the last sunday in any given month:
DECLARE @Date datetime = GETDATE();
SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, EOMONTH(@Date)), EOMONTH(@Date)) As LastSundayDate
Result:
LastSundayDate
30.04.2017 00:00:00
Another example:
SET @Date = DATEADD(MONTH, -1, GETDATE());
SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, EOMONTH(@Date)), EOMONTH(@Date)) As LastSundayDate;
Result:
LastSundayDate
26.03.2017 00:00:00
As SqlZim wrote in his comment, this relies on setting the first day of the month at sunday (using set datefirst 7; before using this query.