SQL Server query to get second thursday of a month

Question

How to get the second Thursday of a month in SQL Server?

For example:

If the month is June 2016, I need to get the date 06/9/2016
If the month is July 2016, I need to get the date 07/14/2016

Possible duplicate of [MSSql Get next relative day of week. (Next Monday, Tuesday, Wed.....)](http://stackoverflow.com/questions/18920393/mssql-get-next-relative-day-of-week-next-monday-tuesday-wed) — Galya, Jun 28 '16 at 06:47

score 5 · Answer 1 · edited Jan 11 '20 at 10:35

5

SELECT 
    DATEADD (
        DAY, (
            DATEDIFF ( DAY, 3, --Day of week where 0 = Mon 
                DATEADD ( MONTH, 
                    DATEDIFF ( MONTH, 0, GETDATE() ), 0
                    )
                ) 
            / 7
        ) * 7 + ( 2 * 7 ), --1 * 7 for week 1, 2 * 7 for week 2, etc.
        3 --Day of week where 0 = Mon 
    ) AS NextDay

Replace GETDATE() to what you want , it will return the first day of your provided date.

edited Jan 11 '20 at 10:35

Arsen Khachaturyan

7,904
4
42
42

answered Jun 28 '16 at 06:54

Kason

787
3
14

1

@keerthi Please accept the answer, if this is helpful for you. – Kason Jun 28 '16 at 08:40
2

Hi @Kason, I know this is really old... any chance you could (really) briefly explain what this code above is doing? I am using it to get the First, Second and Third Wednesday of a month. I changed the +14 to +6, +13, +20 which gives me the correct results but I'm not really understanding why. – reallybadatmath Sep 02 '18 at 11:55
As I understand it the number is the point of reference it is using so if you say plus 14 then it is saying get the Thursday from Today + 14 days so this would give the date of the Thursday in 2 weeks time. I have edited the answer to add a comment showing how it can be adapted for any day and week as took me a while to work it out - hope it is helpful. – Robin Wilson Jan 11 '20 at 01:44

Ed Sansbury · Answer 2 · 2023-06-13T19:24:45.713

This will get the 1st, 2nd, 3rd etc. (@intIteration) Thursday (@strDay) starting at a specified date (@dtToday). You can pass any weekday to @strDay (e.g. 'Wednesday').

CREATE FUNCTION [dbo].[GetDateOnNthDayOfMonth]
(
    @intIteration int = null,
    @strDay varchar(9) = null,
    @dtToday datetime = null
)
RETURNS datetime
AS
BEGIN
    DECLARE @dtReturn datetime = NULL
    DECLARE @dtDate datetime

    SET @strDay = ISNULL(@strDay, 'Monday')
    SET @dtToday = ISNULL(@dtToday,GETDATE())
    SET @dtDate = @dtToday
    SET @intIteration = ISNULL(@intIteration,1) - 1

    WHILE @dtReturn IS NULL
        BEGIN
            IF DATENAME(WEEKDAY, @dtDate) = @strDay
                SET @dtReturn = DATEADD(DAY, @intIteration * 7, @dtDate)
            ELSE
                SET @dtDate = DATEADD(DAY, 1, @dtDate)
        END

    RETURN @dtReturn
END

Use it like this:

SELECT dbo.GetDateOnNthDayOfMonth(2,'Thursday','2016-06-01')
SELECT dbo.GetDateOnNthDayOfMonth(2,'Thursday','2016-07-01')
SELECT dbo.GetDateOnNthDayOfMonth(4,'Wednesday','2020-06-15')

It may be worth providing some additional explanation. I appreciate that you've taken the core question and generalized the solution—but that may not be immediate obvious to someone casually skimming the answers on this thread. Help the community understand why this approach is valuable by offering a quick summary at the top of what you're doing and why it differs from previous answers. — Jeremy Caney, Jun 12 '20 at 19:39

score 0 · Answer 3 · answered Jun 28 '16 at 07:10

simple create a function and pass date to get desire Thursday date

CREATE FUNCTION Get2nd_ThursDay(@CurrentDate DATETIME)
RETURNS DATETIME
AS 
BEGIN
    DECLARE @NextThursday DATETIME

    SELECT @NextThursday = DATEADD(DAY, (DATEDIFF(DAY, 3,  
    DATEADD(MONTH, DATEDIFF(MONTH, 0, @CurrentDate), 0)) / 7) * 7 + 14, 3) 

    RETURN @NextThursday

END

then use in query as

SELECT dbo.Get2nd_ThursDay(getdate())

Hamid · Answer 4 · 2016-06-28T07:10:34.553

You can use following function:

CREATE FUNCTION fn_DayOfMonth
(
    @date datetime,
    @day [int],
    @num int
)
RETURNS datetime
AS
BEGIN
    DECLARE @desiredDate as datetime;
    DECLARE @firstDate as datetime;


    SELECT @firstDate = DATEADD(month, DATEDIFF(month, 0, @date), 0);
    SELECT @desiredDate = DATEADD(DAY, ( ( (7 * @num + @day) - DATEPART(dw, @firstDate) ) % 7 ) + 7, @firstDate);

    RETURN @desiredDate;
END
GO

And use it as follow:

SELECT [dbo].[fn_DayOfMonth]('2016-06-09', 5, 2)

Which first argument is the input date, second one is the day of the week (1 for Sunday and 7 for Sat) and the last argument is the occurrence.

SQL Server query to get second thursday of a month

4 Answers4