Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by jkelly » DateTime Precision
Add to briefcase
37 posts, Page 2 of 4
««1234»»»

DateTime Precision Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Iulian -207023
Posted Monday, November 28, 2011 1:02 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: Saturday, March 16, 2013 9:53 AM
Points: 847, Visits: 768
It is a string comparation.
Dates comparation might be:
IF (CAST( '2011-07-31 00:00:00.000' AS DATETIME) BETWEEN CAST( '2011-07-01' AS DATETIME) and CAST( '2011-07-31' AS DATETIME) )
PRINT 'Yes'
ELSE
PRINT 'No'

Regards,
Iulian
Post #1212312
paul s-306273
Posted Monday, November 28, 2011 1:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:24 AM
Points: 1,158, Visits: 642
Hi jkelly - tricky question!
Post #1212318
skanker
Posted Monday, November 28, 2011 2:22 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 @ 1:57 AM
Points: 692, Visits: 1,022
Thanks for the question and also to the posts gave me something to think about on a cold Monday morning.
Post #1212330
Paul Urban
Posted Monday, November 28, 2011 2:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 06, 2012 3:20 AM
Points: 26, Visits: 28
As several people have pointed out, this "explanation" is bogus.

SQL Server is comparing these values as strings - this has nothing to do with datetime precision. Replace the '2011-07-31 00:00:00.000' with '2011-07-31 REDHERRING' and you get the same result.
Post #1212337
Sunil Chandurkar
Posted Monday, November 28, 2011 2:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 27, 2012 4:24 AM
Points: 253, Visits: 78
I overlooked that it is a STRING comparison and got the answer wrong. (though writer had intention to show datetime comparison)
I updated the code as follows and executed:

DECLARE @DATE DATETIME
SET @DATE = '2011-07-31 00:00:00.001'
IF (@DATE BETWEEN '2011-07-01' and '2011-07-31')
PRINT 'Yes'
ELSE
PRINT 'No'

The output came 'Yes'. However when I changed the @date value to '2011-07-31 00:00:00.002' the result was 'No'. As per my knowledge the @date value should be compared to the end date '2011-07-31 00:00:00.000'. How '2011-07-31 00:00:00.001' be less than or equal to '2011-07-31 00:00:00.000'?

I executed this on SQL Sever 2008 EE Ver. 10.0.4064.0
Post #1212338
Koen Verbeeck
Posted Monday, November 28, 2011 2:41 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 9:06 AM
Points: 9,367, Visits: 6,465
Sunil Chandurkar (11/28/2011)
I overlooked that it is a STRING comparison and got the answer wrong. (though writer had intention to show datetime comparison)
I updated the code as follows and executed:

DECLARE @DATE DATETIME
SET @DATE = '2011-07-31 00:00:00.001'
IF (@DATE BETWEEN '2011-07-01' and '2011-07-31')
PRINT 'Yes'
ELSE
PRINT 'No'

The output came 'Yes'. However when I changed the @date value to '2011-07-31 00:00:00.002' the result was 'No'. As per my knowledge the @date value should be compared to the end date '2011-07-31 00:00:00.000'. How '2011-07-31 00:00:00.001' be less than or equal to '2011-07-31 00:00:00.000'?

I executed this on SQL Sever 2008 EE Ver. 10.0.4064.0


This is because .001 is rounded to .000, giving you the Yes result.
.002 is rounded to .003, giving you the No result. The datatime datatype is only accurate to every 3 milliseconds. This was in a QoTD a few days ago


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1212342
L' Eomot Inversé
Posted Monday, November 28, 2011 2:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:54 AM
Points: 7,086, Visits: 7,138
Disgraceful trick question - it's not about the topic it claims to be about.

However, the utterly wrong explanation suggests that maybe the author didn't even realise that, so maybe the use of "Datetime" in the topic was not an intentional deception.

Tom
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #1212349
Hugo Kornelis
Posted Monday, November 28, 2011 3:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:03 PM
Points: 5,233, Visits: 7,032
Koen Verbeeck (11/27/2011)
Silly trick question...

It would be a silly trick question if the author intended it to trick us.
The "explanation" suggests that the question author fell for the trap himself. He (or she?) simply didn't realize that string comparison would be used, then went on to think up a different explanation - one that obviously contradicts BOL.

It would have been a nice (not silly!) and great trick question if the explanation had read: "SQL Server will use string comparison, d'oh!"

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1212364
Bruce.sexton
Posted Monday, November 28, 2011 3:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 08, 2011 2:08 AM
Points: 2, Visits: 8
So what is the correct query in your origninal question to correctly compare the datetime field with the 2 date fields? In other words how does one truncate the time portion from the datetime field to do the comparison?

Bruce
Post #1212374
Koen Verbeeck
Posted Monday, November 28, 2011 3:47 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 9:06 AM
Points: 9,367, Visits: 6,465
Hugo Kornelis (11/28/2011)
Koen Verbeeck (11/27/2011)
Silly trick question...

It would be a silly trick question if the author intended it to trick us.
The "explanation" suggests that the question author fell for the trap himself. He (or she?) simply didn't realize that string comparison would be used, then went on to think up a different explanation - one that obviously contradicts BOL.

It would have been a nice (not silly!) and great trick question if the explanation had read: "SQL Server will use string comparison, d'oh!"


I've realized that by now. I wasn't really awake this morning when I wrote my reply.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1212382
« Prev Topic | Next Topic »

Add to briefcase
37 posts, Page 2 of 4
««1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.