Post reply

Merry Christmas 2014

  • December 24, 2014 at 10:25 pm

    #296250

    Comments posted to this topic are about the item Merry Christmas 2014

  • December 25, 2014 at 12:13 am

    #1766392

    +7

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • December 25, 2014 at 2:50 am

    #1766398

    +7

    Thanks.

  • December 25, 2014 at 4:35 am

    #1766402

    For those who, like me, had to spend more time than they are willing to admit to understand the logic, here is a breakdown.

    - datediff(yy,-1,getdate()) --> The integer -1 is implicitly converted to the datetime value 1899-12-31T00:00:00 (one day before Jan 1st, 1900, at midnight). Then datediff computes the number of year boundaries between that day and the current date. For any date in the year 2014, the result will be 115.

    - dateadd(yy, [see above], -7) --> The integer -7 is implicitly converted to the datetime value 1899-12-25T00:00:00 (seven days before Jan 1st, 1900, at midnight). Then a number of years equal to the result of the above expression (115 for any date in 2014) is added to this, to arrive at Christmas day in the current year.

    This is not the most understandable way to compute Christmas in the current year. I would never allow this in my production code without at least two lines of comments. And even then, I would push to replace this with

    SELECT CAST (CAST(YEAR(CURRENT_TIMESTAMP) AS char(4)) + '-12-25' AS date);

    But for a Christmas fun question, it is definitely a nice method! (And there are several way more valid uses for similar patterns)

    Mery Christmas!!!

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • December 25, 2014 at 4:58 am

    #1766403

    Thanks Steve.

    I had to once again repeat the DateDiff and DateAdd functions 😉

    Merry Christmas 2014 and Happy New Year 2015.

  • December 26, 2014 at 8:59 am

    #1766478

    Hugo Kornelis (12/25/2014)

    For those who, like me, had to spend more time than they are willing to admit to understand the logic ...

    Hugo, it's reassuring to know that someone with your skills had to spend some time to understand the logic. It took me a while to figure it out.

  • December 29, 2014 at 7:46 am

    #1766780

    I would never do it this way.

  • December 29, 2014 at 1:59 pm

    #1766859

    Iwas Bornready (12/29/2014)

    I would never do it this way.

    I would.

    It's much more fun than messing about with strings as Hugo suggests, because it provides useful mental excercise; but of course I'm no longer responsible for any production SQL code, so I can get away with it - - half a dozen years ago I would have regarded as criminal obfuscation. 😛

    Tom

  • December 30, 2014 at 2:55 pm

    #1767090

    Very helpful Hugo. Thanks for the clarification.

    Andre @sqlinseattle

  • January 2, 2015 at 4:12 am

    #1767361

    Great question 🙂

    And thanks to Hugo for his explanation.

  • January 2, 2015 at 4:24 am

    #1767367

    Thanks Steve.

    Sujeet Singh

  • January 3, 2015 at 5:19 am

    #1767544

    Thanks for the 7 points 😎

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 9, 2015 at 2:25 am

    #1768993

    I also think this is a pretty nifty way of dusting the brains a bit. I don't understand the -7. Can someone please explain this to me? :blush::blush::blush::blush::blush:

    Manie Verster
    Developer
    Johannesburg
    South Africa

    I am happy because I choose to be happy.
    I just love my job!!!

  • January 9, 2015 at 7:00 am

    #1769065

    manie (1/9/2015)

    I also think this is a pretty nifty way of dusting the brains a bit. I don't understand the -7. Can someone please explain this to me? :blush::blush::blush::blush::blush:

    See my previous message in this thread.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • January 11, 2015 at 11:16 pm

    #1769399

    Hugo Kornelis (1/9/2015)

    manie (1/9/2015)

    I also think this is a pretty nifty way of dusting the brains a bit. I don't understand the -7. Can someone please explain this to me? :blush::blush::blush::blush::blush:

    See my previous message in this thread.

    :blush::blush::blush::blush::blush::blush::blush::blush::blush::blush::blush::blush:

    Now I feel even more stupid. I saw that and scrolled right by it. Thanks for the explanation. I learned a lot today.

    Manie Verster
    Developer
    Johannesburg
    South Africa

    I am happy because I choose to be happy.
    I just love my job!!!

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply