New Year's Eve

  • Comments posted to this topic are about the item New Year's Eve

  • Easy QOTD to end the year on good note.

    Thanks Steve and all for making the year wonderful and interactive with QOTD efforts. And a wish you a wonderful year ahead.

    Happy New Year :-):-):-)

  • Nice question to end the year.

    Happy New Year all Family members 🙂

    May 2014 bring joy with enjoy 😎

  • Nice question.

    I spent some time wondering whether the second option was really intended to have that "b" or not, but decided that as it would also need other changes to make it work it didn't matter.

    Happy 2014, everyone.

    Tom

  • It's interesting to point out that for the correct solution the starting date did not have to be Dec 31 but could have been ANY date within 2013.

    Pretty slick there, Steve! :hehe:

  • Interesting question, I had to look close at that one.

    Happy New Year Everyone!!!

  • L' Eomot Inversé (12/31/2013)


    Nice question.

    I spent some time wondering whether the second option was really intended to have that "b" or not, but decided that as it would also need other changes to make it work it didn't matter.

    Happy 2014, everyone.

    +1 The b kind of threw me off a little as well. Thanks for the great question to end the year on.



    Everything is awesome!

  • Nice question. What is most interesting to me is why 23% (as of now) got it wrong.

    I can see thinking that

    select dateadd( ss, -1, datediff(yy, 0, @ThisDate) + 1 )

    is the correct answer if you don't know that the year "0" is 1900. But, I don't see how you can know how to interpret parenthesis and still choose C or D.

    Hope everyone has a great 2014! Our most eastern members are already there.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Very nice tricky question.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • L' Eomot Inversé (12/31/2013)


    Nice question.

    I spent some time wondering whether the second option was really intended to have that "b" or not, but decided that as it would also need other changes to make it work it didn't matter.

    Happy 2014, everyone.

    I started doing the same thing and came to the same conclusion.

    Happy New Year y'all

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • sqlnaive (12/30/2013)


    Easy QOTD to end the year on good note.

    Thanks Steve and all for making the year wonderful and interactive with QOTD efforts. And a wish you a wonderful year ahead.

    Happy New Year :-):-):-)

    +1

    😀

  • Steve, thanks for the question and Happy New Year to all.

  • That is a nice trick that dateadd(yy, datediff(yy, 0, @ThisDate) + 1, 0) returns the first moment of next year. However, it does have the downside that if @ThisDate is one of the newer date&time data types (i.e., datetime2, datetimeoffset), the result is converted back to the old datetime data type.

    This is because DATEDIFF just returns an integer, losing all concept of the data type of @ThisDate, and then DATEADD, with only integers as arguments, defaults to the datetime datatype.

    If the result is cast to datetime2 (either explicitly, or implicitly by assignment to a variable or column of the data type), the loss of microseconds and nanoseconds doesn't really matter since the idea is to zero out the fractional part anyway. However, with datetimeoffset one may need to retain the timezone offset:

    declare

    @ThisDate datetimeoffset(7) = SYSDATETIMEOFFSET()

    ;

    select

    [Input] = @ThisDate

    , [LoseTZ] = CAST(DATEADD( ss, -1, DATEADD(yy, DATEDIFF(yy, 0, @ThisDate) + 1, 0) ) AS datetimeoffset(7))

    , [WithTZ] = TODATETIMEOFFSET(DATEADD( ss, -1, DATEADD(yy, DATEDIFF(yy, 0, @ThisDate) + 1, 0) ), DATEPART(tz, @ThisDate))

    ;

    Sincerely,
    Daniel

  • Aaron N. Cutshall (12/31/2013)


    It's interesting to point out that for the correct solution the starting date did not have to be Dec 31 but could have been ANY date within 2013.

    Pretty slick there, Steve! :hehe:

    The last second of 2013? How can that be on any day but Dec 31?

    Tom

  • L' Eomot Inversé (12/31/2013)


    The last second of 2013? How can that be on any day but Dec 31?

    I meant that the date in the variable could have been any day of the year since only the year portion was utilized. Of course, the last second of the year will be on Dec 31!;-)

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

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