Date of the 3rd Thursday of each quater (Mar, Jun, sep,dec)

• troyrowlands

  SSC Enthusiast

  Points: 146

  More actions

  September 29, 2009 at 6:32 am

  #205208

  I need to work out the date of the 3rd Thursday of each quater (Mar, Jun, sep,dec) and to which of these today's date is the closest but not the Thursday in the past.

  e.g. input = todays date

  output = 17th Dec 2009 (this is the next 3rd thursday of the current quater)

  Thanks

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  September 29, 2009 at 6:39 am

  #1059425

  Use a calendar table...

  http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html%5B/url%5D

  Presumably you know the first date of the quarter so finding the third thursday would be straight

  forward.

  ---

  
  Clear Sky SQL
  My Blog[/url]

• GSquared

  SSC Guru

  Points: 260824

  More actions

  September 29, 2009 at 7:30 am

  #1059460

  Calendar table is definitely the best way to go.

  Here's a mathematical method, using a Numbers table.

  ;

  WITH CTE(Date, Seq)

  AS (SELECT

  DATEADD(day, number, '1/1/2009'),

  row_number() OVER (PARTITION BY DATEPART(year,

  DATEADD(day, number, '1/1/2009')),

  DATEPART(month,

  DATEADD(day, number, '1/1/2009')) ORDER BY number)

  FROM

  dbo.Numbers

  WHERE

  DATEPART(weekday, DATEADD(day, number, '1/1/2009')) = 5

  AND DATEPART(month, DATEADD(day, number, '1/1/2009')) IN (3, 6, 9, 12))

  SELECT

  MIN(Date) AS Date

  FROM

  CTE

  WHERE

  Seq = 3

  AND Date > GETDATE() ;

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• troyrowlands

  SSC Enthusiast

  Points: 146

  More actions

  September 29, 2009 at 8:32 am

  #1059500

  many thanks, this worked perfectly. 🙂