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 Precision Expand / Collapse
Author
Message
Posted Monday, November 28, 2011 1:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 13, 2014 3:36 AM
Points: 1,012, Visits: 1,000
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
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: Monday, November 24, 2014 8:30 AM
Points: 1,417, Visits: 806
Hi jkelly - tricky question!
Post #1212318
Posted Monday, November 28, 2011 2:22 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: Today @ 3:51 AM
Points: 915, Visits: 1,304
Thanks for the question and also to the posts gave me something to think about on a cold Monday morning.
Post #1212330
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 6, 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
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: Wednesday, September 18, 2013 6:40 AM
Points: 253, Visits: 79
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
Posted Monday, November 28, 2011 2:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 13,559, Visits: 11,367
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
Posted Monday, November 28, 2011 2:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:45 AM
Points: 7,860, Visits: 9,606
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
Post #1212349
Posted Monday, November 28, 2011 3:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 6,098, Visits: 8,365
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
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 8, 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
Posted Monday, November 28, 2011 3:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 13,559, Visits: 11,367
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 ««1234»»»

Permissions Expand / Collapse