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 02, 2012 4:24 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:14 PM
Points: 8,285, Visits: 8,735
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 02, 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: Wednesday, April 09, 2014 10:34 AM
Points: 701, Visits: 1,140
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 02, 2012 6:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:46 PM
Points: 5,471, Visits: 23,504
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 02, 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, April 15, 2014 12:05 PM
Points: 3,569, Visits: 72,411
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 02, 2012 7:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:43 PM
Points: 2,763, Visits: 5,907
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 02, 2012 7:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 1,192, Visits: 2,024
Nice question!
Post #1339133
Posted Thursday, August 02, 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 02, 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: Tuesday, April 15, 2014 2:58 PM
Points: 687, Visits: 5,883
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 02, 2012 7:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, April 04, 2014 9:19 AM
Points: 1,224, Visits: 361
A most excellent question for content AND presentation.

+1

Cheers,

Steve
Post #1339183
Posted Thursday, August 02, 2012 7:59 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: Yesterday @ 7:29 AM
Points: 723, Visits: 598
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