Use SQL To Get Thanksgiving days 6

[SQLDenis]

I will give stars to the person(s) who can come up with a better/faster/easier solution to get the next thanksgiving days by using SQL

Here is my code

CREATE TABLE #NumberPivot (Number INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

WHILE @intLoopCounter <(10 *365)
BEGIN
INSERT INTO #NumberPivot
VALUES (@intLoopCounter)

SELECT @intLoopCounter = @intLoopCounter +1
END


SET DATEFIRST 7

DECLARE @dtmStardate DATETIME
SELECT @dtmStardate ='2005-11-24 00:00:00.000'

SELECT MAX((DATEADD(d,number,@dtmStardate))) AS ThanksGivingDay,
DATEPART(yyyy,DATEADD(d,number,@dtmStardate)) AS [Year]
FROM dbo.#NumberPivot
WHERE DATEPART(dw,DATEADD(d,number,@dtmStardate)) = 5
AND DATEPART(mm,DATEADD(d,number,@dtmStardate)) = 11
GROUP BY DATEPART(mm,DATEADD(d,number,@dtmStardate)) ,DATEPART(yyyy,DATEADD(d,number,@dtmStardate))
ORDER BY 1

--By the way Happy Thanksgiving

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Anyway can't give you another star for some reason, i will give it to you in another post
I owe you one


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

(Yet another) idea: Thanksgiving day always falls between November 22nd and 28th. So... can we simply do:

SELECT MIN(dt) AS ThanksGivingDay, YEAR(dt) AS [Year]
FROM 
(	SELECT DATEADD(d,number,@dtmStardate) AS dt
	FROM dbo.#NumberPivot
) X
WHERE MONTH(dt)=11
	AND DAY(dt) BETWEEN 22 AND 28
GROUP BY YEAR(dt)
ORDER BY YEAR(dt)

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

Me again

Or even better: since WHILE loop fills only Thursdays, there is ALWAYS ONLY ONE record per year within Nov 22nd-28th interval. So aggregate function is not necessary:

SELECT dt AS ThanksGivingDay, YEAR(dt) AS [Year]
FROM 
(	SELECT DATEADD(d,number,@dtmStardate) AS dt
	FROM dbo.#NumberPivot
) X
WHERE MONTH(dt)=11
	AND DAY(dt) BETWEEN 22 AND 28
ORDER BY YEAR(dt)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[maswien]

Little further thinking, I like vongunt's idea.

 

[SQLDenis]

Me too, I tried to make mine generic so that i can use it for memorial day and labor day also
I owe a star to the both of you, unfortunately it looks like I can only give 1 star per forum id to a person



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[DotNetGnat]

SQLDenis, i will take the privilege of handing out stars to these brilliant people...

Vongrunt, you never stop working until you come up with a concise solution, do you?

Anyways have stars guys...

-DNG

 

[earthandfire]

I'm rather confused by the complexity of these solutions:

As vongrunt says the daterange is very limited so these seem to do the same job but much more simply:

either:

--Required date must be between 22/11 and 28/11
declare @StartYear int, @EndYear int, @TheYear int
declare @TheDate datetime
set @StartYear = 1900
set @EndYear = 2100
set @TheYear = @StartYear
while @TheYear <= @EndYear
begin
[b]  set @TheDate = '11/22/' + convert(varchar(4),@TheYear)
  while datepart(dw, @TheDate) <> 5
  begin
    set @TheDate = @TheDate + 1
  end[/b]
  print convert(varchar(10),@TheDate,103)
  set @TheYear = @TheYear + 1
end

or:

CREATE FUNCTION FourthThursdayInNovember (@Year int)
RETURNS datetime AS  
BEGIN
  --Required date must be between 22/11 and 28/11
  declare @TheDate datetime
  set @TheDate = '11/22/' + convert(varchar(4),@Year)
  while datepart(dw, @TheDate) <> 5
  begin
    set @TheDate = @TheDate + 1
  end
  return @TheDate
END

then ...

declare @StartYear int, @EndYear int, @TheYear int
declare @TheDate datetime
set @StartYear = 1900
set @EndYear = 2100
set @TheYear = @StartYear
while @TheYear <= @EndYear
begin
  print convert(varchar(10),[b]dbo.FourthThursdayInNovember(@TheYear)[/b],103)
  set @TheYear = @TheYear + 1
end

Looking at the experienced names who have contributed to the other solutions, I am certain that you all thought of the code that I've produced and rejected it. I'd be grateful if someone could explain why?

Thanks

 

[earthandfire]

I've bumped this to the top in the hope that someone will have another look at it.

With VB, VBA and Delphi code, I'm quite happy to look at various alternatives and decide for myself which is the best (better) and why.

I'm new to SQL and as stated above am confused by some of the solutions provided.

I would be grateful if someone could compare and perhaps rate the solutions that have been posted and explain why one is better than the other.

Thanks in advance.

 

[vongrunt]

My $.02...

Unless Earth stops spinning for a while, dates and holidays are static data. That's why permanent calendar table of some kind is good choice - you fill it once and forget. This especially applies to holidays based on lunar/astronomical events, like Easter.

Other than that... minimal information necessary to calculate Thanksgiving day is year itself. Find first Thursday in November for that year then add 3 weeks. Or find first Thursday starting with Nov 22nd, same thing. At this point the rest is about implementation. Here are some possible negative points IMHO:

a) WHILE loop. This equals procedural code, and for this simple case it is not necessary.

b) SET DATEFIRST. If you forget to reset it back within the same batch, weird things may happen (so called "hidden code dependencies"). And SET cannot be used within user-defined functions.

c) ambiguous string-to-date conversions. Server with different language/dateformat settings may give different results. Use either pure date math or explicit CONVERT() format (w/ 3rd argument supplied) or unseparated ISO date format ([yy]yymmdd without /-. delimiters)

d) redundant code of any kind. Makes code less friendly for modifications

Of course - these points are purely academical until $hit happens... and there are ways to comply with a) - d) and make code very cryptic, for example:

declare @year smallint; set @year = 2005
select dateadd(wk, datediff(wk, 0, convert(varchar(4), @year)+'1124'), 3)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[earthandfire]

vongrunt, sorry to be so long in replying.

Thanks for your explanation - it makes a lot of sense.

After posting my earlier suggestion, I had a look at using date arithmetic, using day 0 as a starting point and 0-basing the day of the week. I felt that this would avoid both the need for a look up table and also remove the while loop. My solution was around a dozen lines (include declare and set statements).

You do exactly the same in three lines (assuming declare and set on separate lines) and without having to resort to using abs and %. I don't agree that that is cryptic, I'm just annoyed that it took me around 12 lines .