Use of DATETIME and BETWEEN - Part 2

  • Comments posted to this topic are about the item Use of DATETIME and BETWEEN - Part 2

  • Thanks for a good question on fundamentals of the DATETIME data type.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • I try using the sql server 2000 to retrieve date using Between

    the first data will counted but the last will not counted.

    Example:

    Name between 'A%' and 'C%'

    just will get the A & B data only..

    so ...the answer i think no so correct ...

  • This was removed by the editor as SPAM

  • Nice question. This one should have been 2 points and the one of yesterday only 1 point.

    Ah well 🙂

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

  • weisietan (11/18/2011)


    I try using the sql server 2000 to retrieve date using Between

    the first data will counted but the last will not counted.

    Example:

    Name between 'A%' and 'C%'

    just will get the A & B data only..

    so ...the answer i think no so correct ...

    According to MSDN, the behaviour of BETWEEN and DATETIME is exactly the same as in later versions, meaning that the boundaries of the BETWEEN are included in the result set.

    Your example also uses a string datatype, and not dates.

    http://msdn.microsoft.com/en-us/library/aa258277(v=SQL.80).aspx

    http://msdn.microsoft.com/en-us/library/aa225976(v=SQL.80).aspx

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

  • This was removed by the editor as SPAM

  • weisietan (11/18/2011)


    I try using the sql server 2000 to retrieve date using Between

    the first data will counted but the last will not counted.

    Example:

    Name between 'A%' and 'C%'

    just will get the A & B data only..

    so ...the answer i think no so correct ...

    Your BETWEEN clause translates to:

    WHERE Name >= 'A%' AND NAME <= 'C%'

    This means that a name that is just 'A' will not match (it sorts before 'A%' in any collation I know). A name that is just 'C' will match, as will any name that starts with a C and has a second character that sorts before the % sign in your collation, optionally followed by more characters. Here's a repro:

    CREATE TABLE Test

    (Name varchar(20) COLLATE Latin1_General_CI_AI);

    go

    INSERT INTO Test (Name) VALUES ('A')

    INSERT INTO Test (Name) VALUES ('B')

    INSERT INTO Test (Name) VALUES ('C')

    INSERT INTO Test (Name) VALUES ('A$')

    INSERT INTO Test (Name) VALUES ('B$')

    INSERT INTO Test (Name) VALUES ('C$')

    INSERT INTO Test (Name) VALUES ('A%')

    INSERT INTO Test (Name) VALUES ('B%')

    INSERT INTO Test (Name) VALUES ('C%')

    INSERT INTO Test (Name) VALUES ('Aa')

    INSERT INTO Test (Name) VALUES ('Bb')

    INSERT INTO Test (Name) VALUES ('Cc')

    INSERT INTO Test (Name) VALUES ('A$Z')

    INSERT INTO Test (Name) VALUES ('B$Z')

    INSERT INTO Test (Name) VALUES ('C$Z')

    SELECT Name FROM Test

    WHERE Name BETWEEN 'A%' AND 'C%'

    ORDER BY Name;

    go

    DROP TABLE Test;

    The % sign has a special meaning is LIKE searches only.


    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/

  • Okay, so I knew it rounds the milliseconds to .000, .003, or .007; however, I didn't know when it rounds up or down.

    So, when the time ends in .001, what does it round to?

    same question for .002 and .999 because those are the ones in the QOTD that messed me up.

  • cengland0 (11/18/2011)


    Okay, so I knew it rounds the milliseconds to .000, .003, or .007; however, I didn't know when it rounds up or down.

    So, when the time ends in .001, what does it round to?

    same question for .002 and .999 because those are the ones in the QOTD that messed me up.

    .001 --> .000

    .002 --> .003

    .999 --> .000 (this has the possibility to "jump" to the next day)

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

  • Great question - thank you.

  • Oops...I thot correct answer should be 3,4,5

    Didn't understand why it will round off when datatype is datetime.??

    Regards,

    Skybvi

    Regards
    Sushant Kumar
    MCTS,MCP

  • SKYBVI (11/18/2011)


    Oops...I thot correct answer should be 3,4,5

    Didn't understand why it will round off when datatype is datetime.??

    Regards,

    Skybvi

    If you read the MSDN article referenced in the explanation, you will notice that the datetime datatype is only accurate for every 3 milliseconds. In other words, time goes like this for datetime:

    2011-11-18T14:06:46.000

    2011-11-18T14:06:46.003

    2011-11-18T14:06:46.007

    2011-11-18T14:06:46.010

    ...

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

  • Thanks Dwayne for an excellent question!

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

  • Good question, got it wrong, learned something today about time rounding. Well, re-learned.....:ermm:

    Rich

Viewing 15 posts - 1 through 15 (of 37 total)

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