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 ««12345»»»

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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:21 PM
Points: 7,928, Visits: 9,653
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: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 4,046, Visits: 9,202
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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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: 2 days ago @ 9:23 AM
Points: 1,422, Visits: 2,444
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
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, December 18, 2014 11:59 AM
Points: 747, Visits: 6,259
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: Thursday, August 21, 2014 2:44 PM
Points: 1,282, Visits: 378
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, December 18, 2014 1:57 PM
Points: 849, Visits: 732
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 ««12345»»»

Permissions Expand / Collapse