SQLTeam.com | Weblogs | Forums

Get the particular date


#1

Hi All,

Is there a way to get the second Wednesday date of following months?


#2

There are clever ways to do date arithmetic, for example, see these series of articles by Itzik Ben-Gan. Perhaps one of those techniques could be used in your case as well.

A simple, (but perhaps not the most clever or most efficient) is to use a numbers table (also called a tally table) like this. You can copy this and run to see what it does.

DECLARE @date DATE = GETDATE();

SELECT DATEADD(dd,N,@Date)
FROM
(
	SELECT *, ROW_NUMBER() OVER(ORDER BY N) AS RN
	FROM
		#Tally t
	WHERE
		DATEDIFF(mm,@date,DATEADD(dd,N,@date)) = 1
		AND DATEDIFF(dd,0,DATEADD(dd,N,@date))%7 = 2
)s WHERE RN = 2

Here I am using #Tally as the tally table. If you have a tally table in your database, use that instead. If you don't have one, construct one like shown below before using the code.

CREATE TABLE #Tally (N INT NOT NULL PRIMARY KEY);

;WITH N(n) AS 
(
	SELECT 1 UNION ALL SELECT 1 UNION ALL
	SELECT 1 UNION ALL SELECT 1 UNION ALL 
	SELECT 1 UNION ALL SELECT 1
)
INSERT INTO #Tally
SELECT ROW_NUMBER() OVER (ORDER BY a.n)
FROM N a CROSS JOIN N b;

#3

Using the tally table already given, this code generates only the specific date(s) needed for each month. The logic is:

  1. gen the 14th day of each month, which is latest day that could possibly be the second Wednesday

  2. subtract from the 14th the number of days back to the previous Wednesday -- could be from 0 to 6 days back.

    DECLARE @start_month date
    DECLARE @number_of_months int

    SET @start_month = GETDATE()
    SET @number_of_months = 12

    SELECT DATEADD(DAY, -DATEDIFF(DAY, 2, month_day_14) % 7, month_day_14) AS month_second_wednesday
    FROM #Tally t
    CROSS APPLY (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_month) + t.N - 1, 13) AS month_day_14
    ) AS month_day_14
    WHERE t.N BETWEEN 1 AND @number_of_months


#4

DECLARE @DATE DATETIME
DECLARE @NEXTMONTH DATETIME
DECLARE @WEDNESSDAY DATETIME
Declare @2Week as DATETIME

set @DATE= '20140321'
set @NextMonth = (select dateadd(month,1,dateadd(month,datediff(month,0,@date),0)))
set @2Week = ( select dateadd(dw,7,@NextMonth))
set @wednessday = (
select
case
when datepart(DW,@2Week) = 7 then dateadd(dd,-3,@2Week)
when datepart(DW,@2Week) = 6 then dateadd(dd,-2,@2Week)
when datepart(DW,@2Week) = 5 then dateadd(dd,-1,@2Week)
when datepart(DW,@2Week) = 3 then dateadd(dd, 1,@2Week)
when datepart(DW,@2Week) = 2 then dateadd(dd, 2,@2Week)
when datepart(DW,@2Week) = 1 then dateadd(dd, 3,@2Week)
else @2Week
end
)

select
@DATE as your_date
,@WEDNESSDAY as Next_Month_2nd_Week_Wednessday


#5

If you use this approach, be careful about your DATEFIRST setting. It has to be 7 for the query to work correctly. (SELECT @@DATEFIRST) .

If you have to change the datefirst setting to make the query work, test to make that it does not impact other queries/code.