sqlt-sqldatesql-server-2016

Finding specific day in month SQL


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.


Solution

  • 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
    

    Update

    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.