Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

DATETIME 2 Expand / Collapse
Author
Message
Posted Thursday, August 2, 2012 4:24 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:20 PM
Points: 8,565, Visits: 9,070
Nice question.

But 23:59:59.9999999 is rather easy to round to 100 microseconds, isn't it. So I'm surprised so many have it wrong.


Tom
Post #1339046
Posted Thursday, August 2, 2012 4:29 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
Excellent question. I honestly had to run the code to get the correct answer but got a lesson on datetime2.
Post #1339047
Posted Thursday, August 2, 2012 6:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:36 PM
Points: 5,572, Visits: 24,800
I am rather surprised at the low percentage of correct answers.

Correct answers: 37% (76)
Incorrect answers: 63% (129)
Total attempts: 205



And the implications this could have to those designing a DB, and inadvertently or deliberately using different definitions for a date time object in different tables (based only on each individual table's requirements) and then, when selecting, wondering why Select statements using a join / case / where on a date column do not return the correct data.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1339078
Posted Thursday, August 2, 2012 6:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:35 AM
Points: 3,642, Visits: 72,429
Thanks for making me think... I had to sit there and keep reminding myself how the datetime2 values would round and then tried to keep two mental counts (statement 1 and statement 2) in my head as I did the rounding...

:) glad I got it right, and glad I didn't have to use more than the fingers on one hand for each count :)




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1339112
Posted Thursday, August 2, 2012 7:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:22 PM
Points: 3,359, Visits: 7,270
I'm really proud of me because instead of running the query, I went to SQL help to find out what where the 4 and 7 in the datetime2 declaration.
I've learned something new in the correct way: putting attention, investigating, thinking and, only then, answering.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1339118
Posted Thursday, August 2, 2012 7:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 1,280, Visits: 2,214
Nice question!
Post #1339133
Posted Thursday, August 2, 2012 7:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
bitbucket-25253 (8/2/2012)
I am rather surprised at the low percentage of correct answers.

Correct answers: 37% (76)
Incorrect answers: 63% (129)
Total attempts: 205



And the implications this could have to those designing a DB, and inadvertently or deliberately using different definitions for a date time object in different tables (based only on each individual table's requirements) and then, when selecting, wondering why Select statements using a join / case / where on a date column do not return the correct data.


Speaking for myself, I'm relatively untroubled by missing questions like this. I always answer them based on what I know at the moment of answering, without testing in SSMS or googling. On the other hand, if I were using similar code in a project, I would actually test and research the situation. I doubt I'm the only one answering like that.
Post #1339136
Posted Thursday, August 2, 2012 7:43 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 690, Visits: 5,922
patrickmcginnis59 (8/2/2012)
bitbucket-25253 (8/2/2012)
I am rather surprised at the low percentage of correct answers.

Correct answers: 37% (76)
Incorrect answers: 63% (129)
Total attempts: 205



And the implications this could have to those designing a DB, and inadvertently or deliberately using different definitions for a date time object in different tables (based only on each individual table's requirements) and then, when selecting, wondering why Select statements using a join / case / where on a date column do not return the correct data.


Speaking for myself, I'm relatively untroubled by missing questions like this. I always answer them based on what I know at the moment of answering, without testing in SSMS or googling. On the other hand, if I were using similar code in a project, I would actually test and research the situation. I doubt I'm the only one answering like that.


But, but, but... the points you're missing out on...
Post #1339166
Posted Thursday, August 2, 2012 7:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 9:52 AM
Points: 1,281, Visits: 377
A most excellent question for content AND presentation.

+1

Cheers,

Steve
Post #1339183
Posted Thursday, August 2, 2012 7:59 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 26, 2014 9:20 AM
Points: 825, Visits: 721
patrickmcginnis59 (8/2/2012)
bitbucket-25253 (8/2/2012)
I am rather surprised at the low percentage of correct answers.

Correct answers: 37% (76)
Incorrect answers: 63% (129)
Total attempts: 205



And the implications this could have to those designing a DB, and inadvertently or deliberately using different definitions for a date time object in different tables (based only on each individual table's requirements) and then, when selecting, wondering why Select statements using a join / case / where on a date column do not return the correct data.


Speaking for myself, I'm relatively untroubled by missing questions like this. I always answer them based on what I know at the moment of answering, without testing in SSMS or googling. On the other hand, if I were using similar code in a project, I would actually test and research the situation. I doubt I'm the only one answering like that.


+1

Patrick, you are not the only one answering the QOTD this way. I also missed this one today.

Thanks for the question Bitbucket, and the reinforcement of the nuances of DateTime comparison.

-------------
Brian Smith
Post #1339186
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse