Finding specific day in month SQL

Question

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.

Which version of SQL are you using? The linked post asks for solutions for SQL 2000 but there's been significant enhancements in handling date/time data in more recent versions. A little more detail on what you're trying to do would be useful too - which specific days in which specific months are you looking for? Cheers. — Gareth Lyons, Apr 03 '17 at 17:40
@GarethLyons ... updated the question. Mainly I am looking at October and March, but in general I am looking for a good enough explanation so I can better grasp the concepts here. — Branco, Apr 03 '17 at 17:44
What day of which month? I don't understand what you want in the result set. — Gordon Linoff, Apr 03 '17 at 17:49
Oh... I am looking for the last Sunday of each month. Something that can be used to see if a particular date falls within the last Sunday of March through the last Sunday of October (British Summer Time). — Branco, Apr 03 '17 at 17:50
Thanks. Jan 7th 1900 is used as it's know to be a Sunday. The rest uses the difference between that and GETDATE() or alternatively, /*Your value here*/ to work out the last Sunday of that particular month. But there's got to be better ways to do it in SQL 2016. — Gareth Lyons, Apr 03 '17 at 17:52

Zohar Peled · Accepted Answer · 2017-04-03T18:43:39.077

2

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.

edited Apr 03 '17 at 18:43

answered Apr 03 '17 at 18:03

Zohar Peled

79,642
10
69
121

@SqlZim correct. Updated my answer to add a part referencing this. – Zohar Peled Apr 03 '17 at 18:29
@SqlZim correct. However currently I don't have the time to fix it, so I'll simply remove that part from my answer. – Zohar Peled Apr 03 '17 at 18:43

score 2 · Answer 2 · answered Apr 03 '17 at 18:38

If you are interested, I'll often use a Table-Valued-Function to create dynamic date/time ranges. A tally/calendar table would do the trick as well. However, with a UDF, you can supply Date/Time Range, DatePart and Increment.

Also, being a TVF, it is easy to incorporate into a Sub-Query, Cross Join, etc.

In your case, if you are looking for the last Sunday in March and October

Example

Select D=Max(RetVal) 
 From [dbo].[udf-Range-Date]('2017-01-01','2017-12-31','DD',1)
 Where DateName(WEEKDAY,RetVal) = 'Sunday'
   and DatePart(MONTH,RetVal) in (3,11)
 Group By Year(RetVal),Month(RetVal)

Returns

D
2017-03-26
2017-11-26

The UDF if Interested

CREATE FUNCTION [dbo].[udf-Range-Date] (@R1 datetime,@R2 datetime,@Part varchar(10),@Incr int)
Returns Table
Return (
    with cte0(M)   As (Select 1+Case @Part When 'YY' then DateDiff(YY,@R1,@R2)/@Incr When 'QQ' then DateDiff(QQ,@R1,@R2)/@Incr When 'MM' then DateDiff(MM,@R1,@R2)/@Incr When 'WK' then DateDiff(WK,@R1,@R2)/@Incr When 'DD' then DateDiff(DD,@R1,@R2)/@Incr When 'HH' then DateDiff(HH,@R1,@R2)/@Incr When 'MI' then DateDiff(MI,@R1,@R2)/@Incr When 'SS' then DateDiff(SS,@R1,@R2)/@Incr End),
         cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cte2(N)   As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a, cte1 b, cte1 c, cte1 d, cte1 e, cte1 f, cte1 g, cte1 h ),
         cte3(N,D) As (Select 0,@R1 Union All Select N,Case @Part When 'YY' then DateAdd(YY, N*@Incr, @R1) When 'QQ' then DateAdd(QQ, N*@Incr, @R1) When 'MM' then DateAdd(MM, N*@Incr, @R1) When 'WK' then DateAdd(WK, N*@Incr, @R1) When 'DD' then DateAdd(DD, N*@Incr, @R1) When 'HH' then DateAdd(HH, N*@Incr, @R1) When 'MI' then DateAdd(MI, N*@Incr, @R1) When 'SS' then DateAdd(SS, N*@Incr, @R1) End From cte2 )

    Select RetSeq = N+1
          ,RetVal = D 
     From  cte3,cte0 
     Where D<=@R2
)
/*
Max 100 million observations -- Date Parts YY QQ MM WK DD HH MI SS
Syntax:
Select * from [dbo].[udf-Range-Date]('2016-10-01','2020-10-01','YY',1) 
Select * from [dbo].[udf-Range-Date]('2016-01-01','2017-01-01','MM',1) 
*/

This was suggested also by another developer here. I believe there is something like this being used in a separate part of our application. I might swing back around to this when we replace my quick fix with a real implementation. — Branco, Apr 03 '17 at 18:58
Agreed on the reusable part. I just transformed the smaller stuff into a function for yes/no. It is how it is done already. The code base is slated for refactoring so this may find its way in there. — Branco, Apr 03 '17 at 19:47
@Branco Seems like you know what needs to be done. inheriting someone else's work is always hard and very frustrating. — John Cappelletti, Apr 03 '17 at 19:51

Finding specific day in month SQL

2 Answers2

Update