November 28, 2011 at 2:41 am
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'?:unsure:
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 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 28, 2011 at 2:53 am
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
November 28, 2011 at 3:27 am
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. :laugh:
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!"
November 28, 2011 at 3:41 am
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
November 28, 2011 at 3:47 am
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. :laugh:
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. 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 28, 2011 at 3:47 am
Bruce.sexton (11/28/2011)
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
Check out the previous comments, they have all the explanation you need.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 28, 2011 at 4:57 am
Oops - I got it wrong:blush:
November 28, 2011 at 5:17 am
Good question. I have ran into issues with BETWEEN on several occasions.
http://brittcluff.blogspot.com/
November 28, 2011 at 6:48 am
declare @date datetime
Set @date = '2011-07-31 00:00:00.000'
IF (@date BETWEEN '2011-07-01' and '2011-07-31')
PRINT 'Yes'
ELSE
PRINT 'No'
does return Yes
November 28, 2011 at 6:59 am
vk-kirov (11/27/2011)
Nice tricky question with stupid 'explanation'.Of course, the string (not datetime) '2011-07-31 00:00:00.000' is greater than the other string '2011-07-31', and thus the result of the batch is 'No'.
Thanks for this. The question and the answer bothered me, but once I realized I was doing a CAST/CONVERT in my head (that wasn't there) everything made sense.:-)
November 28, 2011 at 7:00 am
You know, the fact this was a trick question highlights a glaring flaw in T/SQL (or perhaps SQL in general?). Using single quotes for both strings and dates is a brain-dead design decision--period.
Would it be so hard to introduce a new quote character for dates, ala Access?
For example:
#02/21/2008# or #12/31/1899 14:23#
Ambiguity is a nasty design flaw, SQL Server (or SQL in general?) is far enough down the road this should have been fixed by now...
November 28, 2011 at 7:06 am
roger.plowman (11/28/2011)
You know, the fact this was a trick question highlights a glaring flaw in T/SQL (or perhaps SQL in general?). Using single quotes for both strings and dates is a brain-dead design decision--period.Would it be so hard to introduce a new quote character for dates, ala Access?
For example:
#02/21/2008# or #12/31/1899 14:23#
Ambiguity is a nasty design flaw, SQL Server (or SQL in general?) is far enough down the road this should have been fixed by now...
I write SQL for both MSSQL servers and Access. I get those dates mixed up all the time when I'm programming for Access. I keep forgetting to put those #'s instead of single quotes. I do wish they were the same.
November 28, 2011 at 7:10 am
roger.plowman (11/28/2011)
You know, the fact this was a trick question highlights a glaring flaw in T/SQL (or perhaps SQL in general?). Using single quotes for both strings and dates is a brain-dead design decision--period.Would it be so hard to introduce a new quote character for dates, ala Access?
For example:
#02/21/2008# or #12/31/1899 14:23#
Ambiguity is a nasty design flaw, SQL Server (or SQL in general?) is far enough down the road this should have been fixed by now...
That's a good suggestion. It would get rid of some nasty accidental implicit conversion bugs.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 28, 2011 at 7:15 am
roger.plowman (11/28/2011)
Using single quotes for both strings and dates is a brain-dead design decision--period.
Technically, T-SQL is not using single quotes for both strings and dates. It uses single quotes for strings only.
There is no way to have date literals in T-SQL; that's why we have to make do with string literals that will be converted (at execution time) to date or datetime values.
</pedantic>
November 28, 2011 at 7:16 am
My apologies. I have corrected the explanation to note the string comparison.
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply