First wednesday of February & August Every year

Question

Post reply

First wednesday of February & August Every year

Ravi

Old Hand

Points: 333
More actions
November 11, 2022 at 11:03 am
Go to Answer
#4111366

Hi All,
I'm looking for First wednesday of February & August Every year . I have managed to achieve every month but couldn't get for particular month.

First Wednesday of Current month:
SET DATEFIRST 3;
DECLARE @dt date
select @dt= CAST (getdate() as DATE)
select
CASE
WHEN DATEPART(WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dt), 0)) = 1
THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @dt), 0)
ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, @dt), 0) +
(7 - DATEPART(WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dt), 0)) + 1)
END.
but i need first wednesday of Feb & Aug every year, any suggestions please.

thanks

DesNorton

SSC-Insane

Points: 24386

November 11, 2022 at 2:12 pm

Answer

#4111645

Something like this

DECLARE @Year int = 2022;

WITH cteMonthStarts AS (
  SELECT FebStart =  DATEADD(mm, (@year-1900)*12 +1, 0)
       , AugStart =  DATEADD(mm, (@year-1900)*12 +7, 0)
)
SELECT ms.FebStart
     , FebFirstWed = CASE WHEN FebWeek.Wed >= ms.FebStart THEN FebWeek.Wed ELSE DATEADD(dd, 7, FebWeek.Wed) END
     , ms.AugStart
     , AugFirstWed = CASE WHEN AugWeek.Wed >= ms.AugStart THEN AugWeek.Wed ELSE DATEADD(dd, 7, AugWeek.Wed) END
FROM cteMonthStarts AS ms
CROSS APPLY (SELECT CAST(DATEADD(dd, 2 -DATEDIFF(dd, 0, ms.FebStart) %7, ms.FebStart) AS DATE)) AS FebWeek(Wed)
CROSS APPLY (SELECT CAST(DATEADD(dd, 2 -DATEDIFF(dd, 0, ms.AugStart) %7, ms.AugStart) AS DATE)) AS AugWeek(Wed);

or this

WITH cteYears AS (
  SELECT src.Yr
  FROM (VALUES (2020), (2021), (2022), (2023), (2024)
       ) AS src(Yr)
)
, cteMonthStarts AS (
  SELECT y.Yr
       , FebStart =  DATEADD(mm, (y.Yr-1900)*12 +1, 0)
       , AugStart =  DATEADD(mm, (y.Yr-1900)*12 +7, 0)
  FROM cteYears AS y
)
SELECT ms.Yr
     , ms.FebStart
     , FebFirstWed = CASE WHEN FebWeek.Wed >= ms.FebStart THEN FebWeek.Wed ELSE DATEADD(dd, 7, FebWeek.Wed) END
     , ms.AugStart
     , AugFirstWed = CASE WHEN AugWeek.Wed >= ms.AugStart THEN AugWeek.Wed ELSE DATEADD(dd, 7, AugWeek.Wed) END
FROM cteMonthStarts AS ms
CROSS APPLY (SELECT CAST(DATEADD(dd, 2 -DATEDIFF(dd, 0, ms.FebStart) %7, ms.FebStart) AS DATE)) AS FebWeek(Wed)
CROSS APPLY (SELECT CAST(DATEADD(dd, 2 -DATEDIFF(dd, 0, ms.AugStart) %7, ms.AugStart) AS DATE)) AS AugWeek(Wed);

How to post data/code on a forum to get the best help.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Des Norton has the right idea but it can be simplified a bit. You can replace the GETDATE() with a variable in the following, in case you want to turn this into an iTVF...

--
 SELECT  FebWed = DATEADD(dd,DATEDIFF(dd,2,v1.FebDT)/7*7,2)
        ,AugWed = DATEADD(dd,DATEDIFF(dd,2,v1.AugDT)/7*7,2)
   FROM (VALUES(
             DATEFROMPARTS(DATEPART(yy,GETDATE()),2,7)
            ,DATEFROMPARTS(DATEPART(yy,GETDATE()),8,7)
        ))v1(FebDT,AugDT)
;

Also, this is completely independent of the value of DATEFIRST.

Now, the title of this thread says "Every Year"... How is "every year" defined for you???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ravi Old Hand Points: 333 More actions · Answer 2

thanks Both i have now sorted with calendar table.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Ravi wrote:

thanks Both i have now sorted with calendar table.

My advice there is "Watch the Reads".

It would also be nice if you posted the code you ended up using for others to learn from.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

https://www.sqltopia.com/algorithms/date-and-time/get-the-nth-weekday-of-any-interval/

N 56°04'39.16"
E 12°55'05.25"

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 5

instead of thinking like procedural language programmer, build a table for 200 ro 500 years. Why compute constants over and over?

Please post DDL and follow ANSI/ISO standards when asking for help.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

Because if you care about performance, you want to avoid IO.

Calculating "next wednesday" is about 150 times faster than looking it up (using ta table) on just a few million rows.

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

SwePeso wrote:

Because if you care about performance, you want to avoid IO. Calculating "next wednesday" is about 150 times faster than looking it up (using ta table) on just a few million rows.

+1,000,000

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ravi Old Hand Points: 333 More actions · Answer 8

SELECT  TOP 1 [Date]
FROM DimDate
WHERE CalendarYear= DATEPART(YEAR, getdate())
AND WeekdayLongName= 'Wednesday'
AND CalendarMonthNumber = 2
ORDER BY [Date] ASC

same as above for august month

This reply was modified 1 year, 12 months ago by Ravi.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Ravi wrote:

SELECT  TOP 1 [Date]
FROM DimDate
WHERE CalendarYear= DATEPART(YEAR, getdate())
AND WeekdayLongName= 'Wednesday'
AND CalendarMonthNumber = 2
ORDER BY [Date] ASC

same as above for august month

That means that you have to make 2 hits on your DimDate table per year and 2 execution plans per year, which constitute at least 4 pages of logical reads and hope no one's query is blocking the DimDate table. It also means that you need a DimDate table in every database that you'd need to do such a thing or have synonyms to a common one in some utility database somewhere. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)