# Thread: strange date query result

1. Registered User
Join Date
Mar 2004
Posts
163

## 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

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

## "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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

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

6. Registered User
Join Date
Nov 2004
Posts
1,427
Nice Pat!

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

Really nice!

7. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by Wim
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

8. Registered User
Join Date
Nov 2004
Posts
1,427
Originally Posted by Pat Phelan
I do a presentation on Data Architecture that I present at SQL Saturday events.
Are any of those presentations online available (YouTube, ..)?

9. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

10. Registered User
Join Date
Nov 2004
Posts
1,427
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.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

#### Posting Permissions

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