oracle between clause inclusive format HH24:MI

Question

My query looks something like:

select *
from mytable
where date_field between to_date(#from#, 'YYYY/MM/DD HH24:MI')
   and to_date(#to#, 'YYYY/MM/DD HH24:MI')

As an example:

if from = 2012/07/18 00:00 and
   to   = 2012/07/18 00:09

will this include records with timestamp 2012/07/18 00:09:01 to 2012/07/18 00:09:59?

or should I change the statement to:

select *
from mytable
where date_field >= to_date(#from#, 'YYYY/MM/DD HH24:MI')
   < to_date(#to#, 'YYYY/MM/DD HH24:MI')

here substituting from : 2012/07/18 00:00 & to: 2012/07/18 00:10 should give me all records with timestamp between midnight & 9M59S past midnight, which is what I want.

Alessandro Rossi · Answer 1 · 2012-08-12T14:52:27.603

1

The between clause accepts both the interval bounds. I suggest the second option to you

select *
from mytable
where date_field >= to_date(#from#, 'YYYY/MM/DD HH24:MI')
   < to_date(#to#, 'YYYY/MM/DD HH24:MI')

You may find this article interesting.

edited Aug 12 '12 at 14:52

answered Aug 12 '12 at 14:40

Alessandro Rossi

2,432
17
24

eaolson · Answer 2 · 2012-08-11T17:59:26.550

0

Ignoring the date portion of the DATE elements, since both 00:09:01 and 00:09:59 come after your "to" time of 00:09:00, no, this query will not include those records.

If you want to include those records, you will need to extend your "to" time to 00:10:00 or TRUNC your records's timestamps to the nearest minute.

Edit:

If your from and to are only accurate to the minute, I'd do this:

SELECT *
  FROM mytable
 WHERE date_field >= to_date(#from#, 'YYYY/MM/DD HH24:MI') 
   AND date_field <  to_date(#to#,   'YYYY/MM/DD HH24:MI') + 1/24/60/60 /* 1 minute */

And make sure you use bind variable for from and to. Is this ColdFusion? If so, use cfqueryparam.

edited Aug 11 '12 at 17:59

answered Aug 11 '12 at 15:04

eaolson

14,717
7
43
58

Since the dates are inclusive, if I use 00:10:00 wouldn't this include 10? I want to exclude it! – iyerland Aug 11 '12 at 15:06
Correct, it would. BETWEEN is shorthand for "start <= foo and foo <= end". – eaolson Aug 11 '12 at 17:53

Gordon Linoff · Answer 3 · 2012-08-11T16:09:54.923

0

The date conversion is going to convert the values into dates, which contain all date elements. You have not specified seconds in the strings, so these will become 0.

In other words, the range ":01" - ":59" is not included.

Since you are working with strings and the strings have date elements in the proper order for comparison, why not do string compares instead:

where to_char(datefield, 'YYYY/MM/DD HH24:MI') between #from# and #to#

I think this does exactly what you want, without fiddling around with date arithmetic.

You can also change the statement as you propose, by incrementing the #to# column and using "<" instead of between.

edited Aug 11 '12 at 16:09

answered Aug 11 '12 at 15:59

Gordon Linoff

1,242,037
58
646
786

1

Note that would likely prevent the use of an index on `date_field`. – Dave Costa Aug 11 '12 at 16:04
1

This can be handled by creating function based indexes in Oracle . . . http://docs.oracle.com/cd/E14072_01/server.112/e10592/statements_5012.htm. By the way, you would have the same problem with rounding the date or doing other date arithmetic. – Gordon Linoff Aug 11 '12 at 16:08
We do want to use the index as this call needs to be performant! – iyerland Aug 11 '12 at 16:09
@GordonLinoff: Oracle will only skip the index if the function is applied to the field from the table, not if it's applied to the other part (?) of the predicate. – eaolson Aug 11 '12 at 17:54
@eaolson . . . Another solution was to truncate the date to the nearest minute. That has the same problem with the index. Creating a function-based index or using "<" are the best solutions. – Gordon Linoff Aug 11 '12 at 17:56

score 0 · Answer 4 · answered Aug 11 '12 at 17:48

0

You could do something like this:

SELECT *
  FROM mytable
 WHERE date_field between to_date(#from#, 'YYYY/MM/DD HH24:MI')
   AND to_date(#to#||':59', 'YYYY/MM/DD HH24:MI:SS')

answered Aug 11 '12 at 17:48

DCookie

42,630
11
83
92

oracle between clause inclusive format HH24:MI

4 Answers4