Use of BETWEEN and DATETIME

Question

Post reply

Use of BETWEEN and DATETIME

Viewing 15 posts - 46 through 60 (of 66 total)

You must be logged in to reply to this topic. Login to reply

Miles Neale SSChampion Points: 13147 More actions · Answer 1

Nice! It shows that we get what we code, and then wonder why it did not be as we intended and not as we wrote.

Thanks

M.

Not all gray hairs are Dinosaurs!

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 2

Very nice question, thanks!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Olga B SSCommitted Points: 1529 More actions · Answer 3

I spotted the gotcha, and got it right. I do think it would have been a more educational question if it focused just on the date and time element, without the added variable switcheroo. Still, it's a good example of what can easily happen in real code if we are not vigilant.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

I got it right for the most horribly wrong reason. I'm still not familiar with some of the assumptions made in declarations.

I assumed this:

INSERT INTO #DATA VALUES('1 jan 2010 10:30')

Would error, since it didn't have a declared column list and there were two columns, and thus answered 0.

I have a date with the basics, it would seem. Apparently a very good question. :hehe:

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 5

SQL handles the fact that the first column is an identity and identity insert isn't on so it ignores the first column because it knows it's generating that data.

TheSpyder SSC Enthusiast Points: 163 More actions · Answer 6

The trick is from SQL - not the author. Between two dates ought to work whchever way round the dates are presented. Could this be described as a bug? At least its an easy fix and you don't have to write dynamic SQL to get it to work. For those of you who don't know, dynamic SQL is the scrpt you use to get around the fact that you can't use variables whereever you want - another bug?

Sorry, I've had a real bad coding day. Thanks for the post.

Ed B Hall of Fame Points: 3616 More actions · Answer 7

venoym (9/27/2011)
I thought the question was fine. The trickery would have gotten closer to an 80% failed if the possible answers had included the number 4. That was my first red flag that there was a trick when the "correct" answer was not in the answer list.

Yes, I looked for 4, then rechecked and stumbled across the correct answer.

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 8

lemsip (9/28/2011)
venoym (9/27/2011)
I thought the question was fine. The trickery would have gotten closer to an 80% failed if the possible answers had included the number 4. That was my first red flag that there was a trick when the "correct" answer was not in the answer list.
Yes, I looked for 4, then rechecked and stumbled across the correct answer.

Could you guys explain why you think 4 would be the right answer? It would be three, because you'd be searching between midnight on the 2nd to midnight on the 4th if you did it the intuitive way according to the question parameters.

Ed B Hall of Fame Points: 3616 More actions · Answer 9

jeff.mason (9/28/2011)
lemsip (9/28/2011)
venoym (9/27/2011)
I thought the question was fine. The trickery would have gotten closer to an 80% failed if the possible answers had included the number 4. That was my first red flag that there was a trick when the "correct" answer was not in the answer list.
Yes, I looked for 4, then rechecked and stumbled across the correct answer.
Could you guys explain why you think 4 would be the right answer? It would be three, because you'd be searching between midnight on the 2nd to midnight on the 4th if you did it the intuitive way according to the question parameters.

We must have both made the same mistake, dropping one of the records based on the time, but not another.

TheSpyder SSC Enthusiast Points: 163 More actions · Answer 10

I too thought the question was fine - its the answer I have a problem with.

Hands up all those who think this is a bug

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 11

theandrweryder (9/28/2011)
I too thought the question was fine - its the answer I have a problem with.
Hands up all those who think this is a bug

*keeps hands demonstratively down*

The meaning of BETWEEN is well-documented, both in Books Online and in the ANSI standard. You may think it's a lousy design, but it's definitely not a bug; this behaviour is very intentional.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 12

Hugo Kornelis (9/27/2011)
I missed it. My bad for not reading the code accurately enough.
Though, with the reversed order of the assignments, one might ownder if the author was trying to educate us something, or if he was us trying to trick us. I would have liked the question more without that trick,

Hi

for me

also

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Paul White SSC Guru Points: 150467 More actions · Answer 13

Hugo Kornelis (9/27/2011)
I missed it. My bad for not reading the code accurately enough.
Though, with the reversed order of the assignments, one might ownder if the author was trying to educate us something, or if he was us trying to trick us. I would have liked the question more without that trick.

+1

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sushil717 Newbie Points: 1 More actions · Answer 14

i think that main question is hidden in noticing @Date2 is defined first and @Date1 is defined later..

DECLARE @DATE1 DATETIME

DECLARE @DATE2 DATETIME

SELECT @DATE2 = '2 jan 2010'

SELECT @DATE1 = '4 jan 2010'

good puzzle..

TheSpyder SSC Enthusiast Points: 163 More actions · Answer 15

Thanks for that, now I understand - its a well documented, intentional lousy design whereas a bug is an undocumented, unintentional lousy design. By the way, BETWEEN is also well documented in the Oxford English Dictionary.

But we digress, so long as everyone remembers that BETWEEN actually means FROM TO and is computerspeak and not English, we'll be fine.