New Year's Eve

Question

New Year's Eve

Steve Jones - SSC Editor

SSC Guru

Points: 736255
More actions
December 30, 2013 at 9:22 pm

#283937

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

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

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

sqlnaive SSCoach Points: 17437 More actions · Answer 1

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

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

Nice question to end the year.

Happy New Year all Family members 🙂

May 2014 bring joy with enjoy 😎

TomThomson SSC Guru Points: 104773 More actions · Answer 3

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

Aaron N. Cutshall SSCrazy Eights Points: 9011 More actions · Answer 4

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:

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

Mike Hays SSCommitted Points: 1871 More actions · Answer 5

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

Happy New Year Everyone!!!

Dana Medley SSCertifiable Points: 6764 More actions · Answer 6

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!

Thomas Abraham SSChampion Points: 10761 More actions · Answer 7

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

Hany Helmy SSChampion Points: 13921 More actions · Answer 8

Very nice tricky question.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

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

Carlo Romagnano SSC-Insane Points: 22670 More actions · Answer 10

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.jacobs SSCommitted Points: 1830 More actions · Answer 11

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

JediSQL SSCrazy Points: 2118 More actions · Answer 12

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

TomThomson SSC Guru Points: 104773 More actions · Answer 13

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

Aaron N. Cutshall SSCrazy Eights Points: 9011 More actions · Answer 14

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!;-)

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com