Find Specific Date Base on a Control Date 2

[Zurich98]

Hello All,

I'm using SQL 2005. How do I find a second Wednesday base on a date?

For example, date = 10/25/2010. The date I'm looking for is 11/3/2010. Your help/suggestion is greatly appreciated.

Thanks

 

[bborissov]

Check here:

http://www.tek-tips.com/faqs.cfm?fid=6628

http://www.tek-tips.com/faqs.cfm?fid=5074

to get the idea

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[simian336]

I like to use a calendar table. See...

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

Much easier to query it.

Simi

 

[TheBugSlayer]

Hi Zurich.

I love CTEs, so here is my solution involving one:

WITH CTE_DatesTable
AS
(
  SELECT CAST('10/25/2010' AS DATETIME) AS [Date]
  UNION ALL
  SELECT DATEADD(dd, 1, [Date])
  FROM CTE_DatesTable
  WHERE DATEPART(dw, DATEADD(dd, 1, [Date])) <= 4
)
SELECT TOP 1 DATEADD(dd, 7, [Date]) FROM CTE_DatesTable ORDER BY [Date] DESC

The logic is to select the first wednesday from the date given then add 7 to it- because you want two wednesdays from the given date. If you wanted 3 then add 14, so on and so forth.

NB: Wednesday is the 4th day of the week per the U.S. English default, which specifies Sunday as the first day of the week. If you are using a different language you can find out with the @@DATEFIRST function or set the first day with the SET DATEFIRST statement.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[simian336]

Like TheBugSlayer's code I tried

declare @test datetime
set @test = '10/25/2010'
select dateadd(day,7,dateadd(day,4-DATEpart(weekday, @test),@test))

but both only work as long as your day of the week is on or before Wednesday other wise they are both in correct.

Simi

 

[Zurich98]

Thank you all for your helps. I use simian336 code. simple and got what I need.

 

[gmmastros]

It's possible to do this without using datepart so that there is no dependence on your language settings. Anyone got ideas about how this can be done?

Here's a hint:

Spoiler

Declare @Temp Table(TheDate DateTime)

Insert Into @Temp Values('20101024')
Insert Into @Temp Values('20101025')
Insert Into @Temp Values('20101026')
Insert Into @Temp Values('20101027')
Insert Into @Temp Values('20101028')
Insert Into @Temp Values('20101029')
Insert Into @Temp Values('20101030')
Insert Into @Temp Values('20101031')
Insert Into @Temp Values('20111001')
Insert Into @Temp Values('20111002')
Insert Into @Temp Values('20111003')
Insert Into @Temp Values('20111004')

Select TheDate,
DateName(Weekday, TheDate),
DateDiff(Day, '19000101', TheDate) % 7
From @Temp
Order By TheDate

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Simian, I think you' re saying that given Wednesday 10/27/2010 the result should be 11/10/2010. If that is correct then something along the lines of the following should do the trick

...
SELECT TOP 1 CASE 
     WHEN DATEPART(dw, [Date]) = 4 THEN DATEADD(dd, 14, [Date]) ELSE DATEADD(dd, 7, [Date]) END AS [Date] FROM CTE_DatesTable ORDER BY [Date] DESC
...

All this can be made general of course.

George, I know you can using a mathematical algorithm like those described here:

http://en.wikipedia.org/wiki/Calculating_the_day_of_the_week

Please do tell your solution...24 hrs from now if nobody comes up with it before...

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)