Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    162

    Answered: strange date query result

    Why do i get this result?

    nr 1 and 3 is ok. Nr 2 should in my opinion not give me 4 rows.

    1.
    WHERE logdate BETWEEN '20160525 08:04:00' AND '20160525 08:06:19.341'
    Result:
    [logdate]
    2016-05-25 08:05:11.047
    2016-05-25 08:04:13.547
    2016-05-25 08:04:13.547

    2.
    WHERE logdate BETWEEN '20160525 08:04:00' AND '20160525 08:06:19.342'
    result:
    [logdate]
    2016-05-25 08:06:19.343
    2016-05-25 08:05:11.047
    2016-05-25 08:04:13.547
    2016-05-25 08:04:13.547

    3.
    WHERE logdate BETWEEN '20160525 08:04:00' AND '20160525 08:06:19.343'
    Result:
    [logdate]
    2016-05-25 08:06:19.343
    2016-05-25 08:05:11.047
    2016-05-25 08:04:13.547
    2016-05-25 08:04:13.547


    [logdate] is datetime
    SQL ver is 11.0.5613

    thx in advance
    Last edited by mrpcguy; 05-25-16 at 05:10.

  2. Best Answer
    Posted by Pat Phelan

    "Argh! I didn't think that one quite all the way through.... The constants ALSO need to be coerced to DATETIME2! The following example demonstrates why:
    Code:
    SELECT t, Cast(t AS DATETIME) AS d, Cast(t AS DATETIME2) AS d2
       FROM (VALUES ('20160525 08:06:19.340')
    ,       ('20160525 08:06:19.341')
    ,       ('20160525 08:06:19.342')
    ,       ('20160525 08:06:19.343')
    ,       ('20160525 08:06:19.344')
    ,       ('20160525 08:06:19.345')
    ,       ('20160525 08:06:19.346')
    ,       ('20160525 08:06:19.347')
    ,       ('20160525 08:06:19.348')
    ,       ('20160525 08:06:19.349')
            ) AS z (t)
    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Precision is your problem. Changing the column from DATETIME to DATETIME2 will fix that by increasing your precision.

    People give me grief about how much time I spend on data types and their impact on solutions, but this is a prime example of someone doing a good job but still being hamstrung by a data type that doesn't quite do what they expected!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Argh! I didn't think that one quite all the way through.... The constants ALSO need to be coerced to DATETIME2! The following example demonstrates why:
    Code:
    SELECT t, Cast(t AS DATETIME) AS d, Cast(t AS DATETIME2) AS d2
       FROM (VALUES ('20160525 08:06:19.340')
    ,       ('20160525 08:06:19.341')
    ,       ('20160525 08:06:19.342')
    ,       ('20160525 08:06:19.343')
    ,       ('20160525 08:06:19.344')
    ,       ('20160525 08:06:19.345')
    ,       ('20160525 08:06:19.346')
    ,       ('20160525 08:06:19.347')
    ,       ('20160525 08:06:19.348')
    ,       ('20160525 08:06:19.349')
            ) AS z (t)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #4
    Join Date
    Mar 2004
    Posts
    162
    ah works like a charm... thx using cast to datetime2 indeed show me correct result in my query

  6. #5
    Join Date
    Nov 2004
    Posts
    1,425
    Provided Answers: 4
    Nice Pat!

    I looked at it but I couldn't find the reason.

    Really nice!
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Wim View Post
    Nice Pat!

    I looked at it but I couldn't find the reason.

    Really nice!
    I do a presentation on Data Architecture that I present at SQL Saturday events. I spend a lot of time (about a third of the presentation) on Data Types and some of the "gotchas" that seem perfectly reasonable at first and will work some or most of the time. The problem is the edge cases (like this one), and even one of those edge cases renders the column undependable and therefore useless in the long run.

    People forever give me grief right after the session, but then a week or a month later I get rather sheepish emails from a few folks telling me how they just discovered why one of their jobs crashes 2-5 months out of the year for no obvious reason... Due to an improper data type!

    People give me grief, until they get caught by it!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #7
    Join Date
    Nov 2004
    Posts
    1,425
    Provided Answers: 4
    Quote Originally Posted by Pat Phelan View Post
    I do a presentation on Data Architecture that I present at SQL Saturday events.
    Are any of those presentations online available (YouTube, ..)?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, not yet anyway.

    I've considered two different ways to approach that. One way is to have someone shoot video of my sessions, which is easy and provides a more "live" feel. I think that a more professional way would be to create a PowerPoint video and distribute the deck with audio. This would be smaller and much more professional looking.

    Do you (or anyone else) have a preference?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #9
    Join Date
    Nov 2004
    Posts
    1,425
    Provided Answers: 4
    I prefer the solution in witch we get free drinks and tapas during the presentation.

    English is not my mother's tongue, I sometimes have problems understanding what is said. Subtitles solve that problem.

    But it seems more appropriate that you decide what is releasable for you: everything comes at a cost, be it in time or money.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  11. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    smart aleck reply...
    do both and see which gets the best response

    more honest reply... you are not a paid professional presenter, so although that would tend to go against the idea of a video clip I'd suggest a video clip of an actual presentation, As you are not (neccesarily) earning from the presentation then it seems silly to spend time on creating a professional presentation

    why?
    aside from its NOT death by powerpoint
    it isn't going to require much (more) of your time
    and we all get to snigger seeing you talk
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •