Thanksgiving

  • Comments posted to this topic are about the item Thanksgiving

  • This was removed by the editor as SPAM

  • 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.

  • "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.

  • What is "Thanksgiving?".

    You never know: reading my book: "All about your computer" might just tell you something you never knew!
    lulu.com/kaspencer

  • 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.

  • The correct answer is 284, oh wait that was Canada, Eh!

  • Anyone else go cross-eyed trying to read #1?

  • 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.

  • 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

  • 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.

  • 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

  • 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:

    In Theory, theory and practice are the same...In practice, they are not.
  • 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.

    ----------------------------------------------------

  • 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