SQL "Between" Not Inclusive

SQL between not inclusive

It is inclusive. You are comparing datetimes to dates. The second date is interpreted as midnight when the day starts.

One way to fix this is:

SELECT *
FROM Cases
WHERE cast(created_at as date) BETWEEN '2013-05-01' AND '2013-05-01'

Another way to fix it is with explicit binary comparisons

SELECT *
FROM Cases
WHERE created_at >= '2013-05-01' AND created_at < '2013-05-02'

Aaron Bertrand has a long blog entry on dates (here), where he discusses this and other date issues.

Noninclusive BETWEEN in SQL

BETWEEN..AND.. is basically syntactic sugar for combination of >= AND <= operators, with same performance.

If expr is greater than or equal to min and expr is less than or equal
to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent
to the expression (min <= expr AND expr <= max)

For non-inclusive range checking, you can use > AND < operators:

SELECT * FROM schedule 
WHERE x > startTime AND 
      x < endTime

MySQL between clause not inclusive?

The field dob probably has a time component.

To truncate it out:

select * from person 
where CAST(dob AS DATE) between '2011-01-01' and '2011-01-31'

Microsoft SQL between statement for characters is not inclusive?

It is inclusive.

You don't get the results you want because any string beginning with 'd' and longer than 1 character is greater than 'd'. For example 'da' > 'd'.
So, your query would return all values starting with 'a', 'b', 'c', and a value 'd'.

To get the results you want use

select * from tblprofile where firstname >= 'a' and firstname < 'e'

Does MS SQL Server's between include the range boundaries?

The BETWEEN operator is inclusive.

From Books Online:

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.

DateTime Caveat

NB: With DateTimes you have to be careful; if only a date is given the value is taken as of midnight on that day; to avoid missing times within your end date, or repeating the capture of the following day's data at midnight in multiple ranges, your end date should be 3 milliseconds before midnight on of day following your to date. 3 milliseconds because any less than this and the value will be rounded up to midnight the next day.

e.g. to get all values within June 2016 you'd need to run:

where myDateTime between '20160601' and DATEADD(millisecond, -3, '20160701')

i.e.

where myDateTime between '20160601 00:00:00.000' and '20160630 23:59:59.997'

datetime2 and datetimeoffset

Subtracting 3 ms from a date will leave you vulnerable to missing rows from the 3 ms window. The correct solution is also the simplest one:

where myDateTime >= '20160601' AND myDateTime < '20160701'

Query between date range, INCLUSIVE

The operators you're looking for are >= and <= . I believe you're using =<.

To represent a TIMESTAMP or DATETIME as a string, you must use YYYY-MM-DD. You are using MM/DD/YYYY. This will not work properly.

Notice that if you want to choose TIMESTAMP values that occur on a particular date range, the best way to do it is with this sort of query. This will get items with timestamps from any time on Aug 8,9, 10.

select id, created 
  from employees 
 where created >= '2014-08-08'
   and created <  '2014-08-10' + INTERVAL 1 DAY 
 order by created desc;

The end of the range (created < '2014-08-10' + INTERVAL 1 DAY) takes everything up to but not including midnight on the last day of the range you want.

What you have starts one second after midnight. This could mess you up if some of your records don't have any time on them, just date. If you have a record, for example, dated 2014-08-08 without any time specified, your 00:01 query will not pick it up.

SQL: Why is BETWEEN operator inclusive?

Yes, BETWEEN is inclusive for both bounds as defined in ANSI SQL, and in all databases that I know.

Postgres is no exception:

The BETWEEN predicate simplifies range tests:

a BETWEEN x AND y

is equivalent to

a >= x AND a <= y

Chances are that your teacher does understand this the same way.

If you want a non-inclusive predicate, then you need to be explicit about it:

SELECT * FROM students WHERE age > 18 AND age < 20

BETWEEN is not behaving in an inclusive manner

The timestamp '2018-10-05' is understood to be 2018-10-05T00:00:00.000Z, so your range is from 2018-10-05T00:00:00.000Z to 2018-10-05T00:00:00.000Z, only one millisecond. Include the hours, minutes, and seconds in your query, or increment the end date by one day when both the begin date and end date are the same day.

To avoid the problem a little more reliably, you might consider not using BETWEEN with dates at all. Use something like TIMESTAMP >= '2018-10-05' AND TIMESTAMP < '2018-10-06' instead.

---

Related Topics