Matching value on date (year, month, day) but not time

Question

In this answer, it's suggested to use teh following syntax for matching against a given date, all day long.

select * from Info
  where DateColumn 
    between '2014-08-25 00:00:00' 
    and '2014-08-25 23:59:59'

Besides the fact that we're missing the last second of each day, which practically perhaps isn't a large issue but principally might be a deal-breaker, I don't see why not use the simple expression below.

The matching is done with on a semi-open interval with the upper bound being exclusive (i.e. fromAndInclusive <= date < toButNotInclusive) and a date without any time specified is assumed to be at midnight (i.e. 00:00:00.000).

select * from Info
  where DateColumn 
    between '2014-08-25' and '2014-08-26'

Please note that I'm not even close to be claiming any level of competence when it comes to SQL so this question shouldn't be been as pointing out any errors. I'm cocky otherwise but when it comes to DBs, I've been humbled once or twice. :)

Why do you say you're missing the last second of each day? 2014-08-26 isn't *that* day :P — Kritner, Aug 27 '14 at 16:03
Once that last second ticks over it's a new day... There is no `'2014-08-25 24:00:00'`. However using between is not a good idea and you should use. `where DateColumn >='2014-08-25 00:00:00' and DateColumn<='2014-08-25 23:59:59'` — Zane, Aug 27 '14 at 16:05
@Kritner I mean *the last second* but of **25th**, i.e. any time on form *2014-08-25 23:59:59.xxx*. — Konrad Viltersten, Aug 27 '14 at 16:09
@Zane You're write that once the last second ticks over it's the next day. But it takes a second for that to happen and **that** is the time of **today** that we're missing from the comparison. Also, why isn't it a good idea to use *between/and*? — Konrad Viltersten, Aug 27 '14 at 16:11
Use what @Lamak said, `WHERE DateColumn >= '20140825' AND DateColumn < '20140826'`. See this: **[What do BETWEEN and the devil have in common?](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common)** — ypercubeᵀᴹ, Aug 27 '14 at 16:11
@Lamak or even when `Cast(dateColumn as date) = '2014-08-25'` — Zane, Aug 27 '14 at 16:12
@Zane Yeah, but then you might negate the possibility of using an index on that column — Lamak, Aug 27 '14 at 16:14
@ypercube So the original answer that I'm linking to **is** missing a second, right? Yey! I got something right in SQL! Also - you should undelete your deleted reply. It's always good to see other approaches. — Konrad Viltersten, Aug 27 '14 at 16:18
@Konrad Yes. But your 2nd query will have the same problem, in reverse. It will add the first point in time of the next day. `'2014-08-26'` and `'2014-08-26 00:00:00'` are the same thing. — ypercubeᵀᴹ, Aug 27 '14 at 16:20
@ypercube Just read the link you've provided. I think that's incorrect info. When I shoot the dates at my DB, it seems that the upper bound **is** exclusive. Not saying that one should use *between*. It might very well be evil. Just pointing out that in my DB, the interval is semi-open. Weird... — Konrad Viltersten, Aug 27 '14 at 16:21
No, it's not. You are wrong. `BETWEEN` is inclusive, always. See the [SQL-Fiddle](http://sqlfiddle.com/#!3/55041/1). — ypercubeᵀᴹ, Aug 27 '14 at 16:22
@ypercube In that case, I can't explain why the match in my question doesn't include the post dated 26th at midnight... The fiddle came out empty. Did you save it before posting? :( — Konrad Viltersten, Aug 27 '14 at 16:27
@KonradViltersten It's not about "thinking" that it might be incorrect, [BETWEEN](http://msdn.microsoft.com/en-us/library/ms187922(v=sql.100).aspx) **is** inclusive, it says so in the documentation: "BETWEEN returns TRUE if the value of test_expression is greater than **or equal** to the value of begin_expression and less than **or equal** to the value of end_expression." — Lamak, Aug 27 '14 at 16:27
@Lamak I'm **not** saying you're wrong. I'm saying that I don't understand why I can't reproduce the behavior expected based on that definition. :( I'm very inexperienced with SQL. — Konrad Viltersten, Aug 27 '14 at 16:29
@Konran Probably because the datetime stored in your database does not have a time part of exactly `00:00:00.000`. Or (the horror) you are not using datetime but a char column. — ypercubeᵀᴹ, Aug 27 '14 at 16:29
@ypercube Ah! There it was. The darned thing had a tiny, tiny tail of a few fractions of a second. Now I'm happy again! — Konrad Viltersten, Aug 27 '14 at 16:31

score 3 · Accepted Answer · edited Aug 27 '14 at 16:32

Both will return incorrect results.

Between is inclusive at both ends so your first query

select * from Info
  where DateColumn 
  between '2014-08-25 00:00:00' 
  and '2014-08-25 23:59:59'

will ignore anything in the last second of 2014-08-25 as you point out.

The second query you propose is too greedy. It will include anything at exactly midnight on the 26th i.e. 2014-08-26 00:00:00.

select * from Info
  where DateColumn 
  between '2014-08-25' and '2014-08-26'

To get the results you need you should use >= and < (as @Lamak) points out in the comments:

select * from Info
  where DateColumn >= '2014-08-25' and DateColumn < '2014-08-26'.

Given the following data in the table:

2014-08-25 23:59:59
2014-08-25 23:59:59.500
2014-08-26 00:00:00
2014-08-26 00:00:00.500
2014-08-26 00:00:01

The first query matches just:

2014-08-25 23:59:59.000

The second matches:

2014-08-25 23:59:59.000
2014-08-25 23:59:59.500
2014-08-26 00:00:00.000 --this is wrong!

and finally the third (correctly) matches:

2014-08-25 23:59:59.000
2014-08-25 23:59:59.500

score -1 · Answer 2 · answered Aug 27 '14 at 15:54

-1

select * from Info
  where DateColumn >='2014-08-25 00:00:00' 
    and datecolumn<='2014-08-25 23:59:59'

answered Aug 27 '14 at 15:54

Adi

232
1
9

Not sure what you mean. My question is why not use the syntax. Also, you seem to have the same error that I've pointed out in my question. Would you like to check it again, please? – Konrad Viltersten Aug 27 '14 at 16:07
What error are you referring to? I'm a bit unclear on what you're trying to solve, if not just looking for another way to do the same query? – Kritner Aug 27 '14 at 16:11
1

@Kritner He's missing e.g. 23:59:59.333. His query is equivalent to the first one. But the second one, that **I** am suggesting constituted an interval which is one second longer. His interval covers 86399 seconds, while mine stretches over 86400 seconds. – Konrad Viltersten Aug 27 '14 at 16:12
1

Why is this answer still here and why is is upvoted? It doesn't answer the question, it only has the same query as the OP has tried (and knew it's wrong). – ypercubeᵀᴹ Aug 27 '14 at 16:35

Matching value on date (year, month, day) but not time

2 Answers2