November 23, 2016 at 10:32 pm
Comments posted to this topic are about the item Thanksgiving
November 23, 2016 at 10:33 pm
This was removed by the editor as SPAM
November 24, 2016 at 12:47 am
Which of these queries will work consistently on every Thanksgiving?
Actually none since limitations of the data type but number 2 works for more years then number 1 so well ... I lost one point there 🙂
Number 1 fails for years before 1753 because of the implicit conversion of the zeros to a datetime in the datediff/dateadd functions and it fails for years after 9997 because dateadd creates a date after 9999-12-31.
November 24, 2016 at 3:10 am
"Number 4 works for this year only."
Actually it doesn't even work for this year.
SELECT DATEPART(DAYOFYEAR, SYSDATETIME()); gives 329
SELECT DATEDIFF( DAY, '20160101', SYSDATETIME()); gives 328
Happy Thanksgiving to all in the USA.
November 24, 2016 at 5:13 am
What is "Thanksgiving?".
You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer
November 24, 2016 at 7:31 am
sipas (11/24/2016)
"Number 4 works for this year only."Actually it doesn't even work for this year.
SELECT DATEPART(DAYOFYEAR, SYSDATETIME()); gives 329
SELECT DATEDIFF( DAY, '20160101', SYSDATETIME()); gives 328
Happy Thanksgiving to all in the USA.
Yes, the explanation is confusing, but the result of these queries listed below is Thanksgiving in this year.
SELECT DATEADD (dd, DATEDIFF( DAY, '20160101', SYSDATETIME()), '20160101');
-- OR
SELECT DATEADD (dd, 328, '20160101');
Yet thanks to Steve for this question. 🙂
Regards G. V.
November 24, 2016 at 7:55 am
The correct answer is 284, oh wait that was Canada, Eh!
November 24, 2016 at 8:10 am
Anyone else go cross-eyed trying to read #1?
November 24, 2016 at 8:32 am
Mikael Eriksson SE (11/24/2016)
Which of these queries will work consistently on every Thanksgiving?
Actually none since limitations of the data type but number 2 works for more years then number 1 so well ... I lost one point there 🙂
Number 1 fails for years before 1753 because of the implicit conversion of the zeros to a datetime in the datediff/dateadd functions and it fails for years after 9997 because dateadd creates a date after 9999-12-31.
From Wikipedia: "Thanksgiving has been celebrated nationally on and off since 1789, after a proclamation by George Washington.[2] It has been celebrated as a federal holiday every year since 1863..."
There was no US Thanksgiving before 1789 at the earliest, so #1 does not fail at the low end.
Depending on how you define "nation", the duration of the longest lasting nation in history ranges from hundreds of years to about 3,000 years. So it's likely (as much as many of us would hate to admit it) that the US will not last until 9,997 AD. So #1 is likely to continue to work as long as the US holiday of Thanksgiving exists.
November 24, 2016 at 9:23 am
Quite a fun question.
I'm somewhat amazed that up to now about a fifth of people answering seem to believe that the result of dateadd is an integer rather than a date, and about a quarter believe that there will be more than 365 days in every year after 2016 (if people didn't believe such nonsense they wouldn't think 3 or 4 could be correct).
A quarter of people believing that 1 would deliver wrong answers is a bit surprising too, since all it requires is to notice that the daynumber of a day in a year is just 1 greater than the number of days after the first day of the same year that day is, which doesn't strike me as anything surprising. But I guess it isn't obvious to everyone what that query is doing, especially not to antone who doesn't use tally tables or is still on SQL Server 2005 or earlier.
Tom
November 24, 2016 at 11:33 pm
This is the kind of question that confuses me because I would never figure the value out (day of the year) by any of the proposed ways. A double cross join to generate a temp table of 1000 rows wrapped up in a CTE? :sick:
It is even more annoying when it happens on a certification exam. :/
And I am not the only one who doesn't like the query. The query processor doesn't like it either. Take a loot at the execution plan (image).
I would do this one this way:
select datediff(day, datetime2fromparts(year(sysdatetime()), 1, 1, 0, 0, 0, 0, 0), SYSDATETIME())+1
The fun part: statistics. The original query (on my computer) required 81 ms, while my proposed query took 0ms and didn't generate e query plan at all.
My 2 cents. 🙂
Happy thanksgiving all.
November 25, 2016 at 7:48 am
Thanks for the question.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 25, 2016 at 8:02 am
If you will run the queries 1, 2 and 4 another day than thanksgiving day it will return another number of days. It's not OK. :hehe:
November 25, 2016 at 9:29 am
sipas (11/24/2016)
"Number 4 works for this year only."Actually it doesn't even work for this year.
SELECT DATEPART(DAYOFYEAR, SYSDATETIME()); gives 329
SELECT DATEDIFF( DAY, '20160101', SYSDATETIME()); gives 328
Happy Thanksgiving to all in the USA.
Right. It just comes down to arithmetic. Assuming today was January 2... the day of the year is 2 but the difference between today and yesterday (Jan 2 and Jan 1 ) is 1 day. These would not be equal ever.
----------------------------------------------------
November 26, 2016 at 9:08 am
I thought it was a great question, but then again, I like date math. I hope everyone had a good Thanksgiving.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply