Post reply

Use of BETWEEN and DATETIME

  • September 27, 2011 at 3:28 am

    #1386996

    Sneaky!

    I've been bitten too many times by this sort of thing when debugging applications to be fooled though 🙂

  • September 27, 2011 at 3:33 am

    #1387000

    Good question for testing the basics, however the author was definitely being sneaky with the reversed assignment. If the intention was to truly test people's understanding of BETWEEN and DATETIME, then the assignment should have been in logical sequence to easily focus on the real issue being tested.

    I do wonder though how many people would have got it right even with the real issue being obvious... all we know is that a lot of people tripped up on the trick, not a relevant stat in my view.

    Anyway, you didn't fool me... ha!! 😛

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • September 27, 2011 at 4:20 am

    #1387021

    I must say (after the coffee and a lunch), that this question is educational in two ways

    - datetime and between

    - careful reading of code

    So I conclude the question is little tricky, but a good one.


    See, understand, learn, try, use efficient
    © Dr.Plch

  • September 27, 2011 at 5:06 am

    #1387034

    good question!!! thanks!

    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!

  • September 27, 2011 at 5:19 am

    #1387037

    Hi everybody

    Thanks for taking the time to answer the question and for your feedback.

    I'd like to explain the reasoning behind this question. There were several things that I wanted to highlight with this.

    Firstly I wanted to demonstrate that queries that are semantically identical in English are not so in SQL. Whereas 'Date between Date1 and Date2' and 'Date between Date2 and Date1' mean the same thing in English, they are different when translated into SQL. It also demonstartes some of the conversions that occur from the parser.

    This is similar to a situation I ran into a while back and I overlooked the above construct as I didn't initially see anything wrong with the BETWEEN clause. This resulted in a lot of wasted time and beard stroking trying to debug the procedure in question. It was a lesson learnt into how higher level constructs are parsed into lower level constructs.

    There was an element of trickery in the question, however I feel that for people to get maximum benefit from the question, then they needed to get it wrong. This would force the person answering to question their assumptions about what was actually happening. I may however be wrong 😉

    I'm hoping that people learnt something though.

  • September 27, 2011 at 5:35 am

    #1387041

    I was originally going to post something saying that the question, which I got right, was needlessly tricky. However, on further reflection, I thought it was perfectly fair. How many times have I gone round and round with a bit of code that wasn't producing the expected results, only to find that the problem was my assumptions about what the code SHOULD be doing. Just like in conversation with a person, I need to focus more on what is actually being said, and not what I assume is being said.

    Thanks for the question.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • September 27, 2011 at 5:35 am

    #1387042

    Thanks for the question. I almost missed it until I read it a second time and caught the trick order of Date2 and Date1.

    http://brittcluff.blogspot.com/

  • September 27, 2011 at 5:40 am

    #1387044

    I do agree the question would have been more instructive without the "gotcha" but I can't argue about the answer.

    EDIT: And after reading Thomas Abraham's response, I take back my previous sentence. It's not the asker's fault I didn't pay closer attention. That could just as easily have been my code. Good question.

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • September 27, 2011 at 5:58 am

    #1387055

    good question

  • September 27, 2011 at 6:17 am

    #1387073

    Honestly, this was a good question, even with the trick. Someone not versed in what BETWEEN represents might see BETWEEN '2011-01-05T00:00:00' AND '2011-01-01T00:00:00' as logically including '2011-01-03T00:00:00'

    The important thing to take from this question, in my mind, is that @X BETWEEN @Y AND @Z really means (@X >= @Y AND @X <= @Z), and this is what is shown in the query execution plan, anyway.

    Thanks for the question.

    Matt

  • September 27, 2011 at 6:37 am

    #1387096

    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.

  • September 27, 2011 at 6:43 am

    #1387100

    I see this as no trick. Why would anyone make the assumption that date1 is the from date and date2 was the to date? Now, if they were named fromdate and todate, it would have been tricky.

    I suppose it's easy to make assumptions based on what we think the results should be and not reality. 😉

  • September 27, 2011 at 6:47 am

    #1387102

    honza.mf (9/27/2011)

    A little bit tricky. For me it's impossible to notice the reverted order of assignments before a coffee.

    That's what caused me to miss it too.

    Always makes me wonder what they are testing for. If it was the use of BETWEEN and DATETIME, why not just put the dates in the query like below. What they are really checking is if you notice the reversed assignments. The declaration was Date1 then Date2 but then the assignment was Date2 then Date1. Why would they do that other than to try to catch you with a trick question.

    SELECT *

    FROM #data

    WHERE sampledate BETWEEN '4 jan 2010' AND '2 jan 2010'

    I would have answered correctly if it was written this way instead.

  • September 27, 2011 at 6:51 am

    #1387104

    OCTom (9/27/2011)

    I see this as no trick. Why would anyone make the assumption that date1 is the from date and date2 was the to date?

    I did not make that assumption. I saw in the query that date1 was the fom date and date 2 was the to date.

    What tricked me was the reversed order of assignment. Most people tend to declare and assign variables with a number at the end in numerical order. That has been done with the declaration here, but not with the assignment.

    I focused on the actual dates being assigned and overlooked that the left hand side of the assignment was slightly different from what I expected.

    That does not make this a bad question; crazy things like that happen in real code all the time. It jusut makes this question test something quite unrelated to what the title suggests. Most people who got this wrong will probably have done so bacause they didn't read the code careful enough, not because they fail to understand how BETWEEN works. Or how BETWEEN works with DATETIME.

    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/

  • September 27, 2011 at 7:03 am

    #1387115

    Scary question, I went with what the answer was but I was ready for a long discussion over a typo.

Viewing 15 posts - 16 through 30 (of 66 total)

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