2

I am working on a query that provides the records displayed on a report and noticed that the previous developer used '=<' and '>=' to compare two datetimes that are parameters of said query. These parameters are passed as strings and are not converted into datetime objects. I noticed that, in the query results, I'm missing data from about two days. I'd like to know if there is any difference in precision when comparing these datetimes using '=<' and '>=' instead of using 'between' (which could be what is causing the loss of data in the results).

For completeness, here is a simple example of the current comparison used:

Declare @DateTo varchar(max) = '2013-07-29'
Declare @DateFrom varchar(max) = '2013-07-07'

SELECT * FROM Records WHERE Date =< @DateTo AND Date >= @DateFrom
m-oliv
  • 419
  • 11
  • 27
  • look at this http://stackoverflow.com/questions/1630239/sql-between-vs-and – zxc Aug 06 '13 at 08:05
  • No, there is no precision loss. What is your actual problem? – Daniel Hilgarth Aug 06 '13 at 08:06
  • I was losing data on the query results and I wasn't sure if the way the comparison was made had any influence. – m-oliv Aug 06 '13 at 08:07
  • 1
    If your `Date` column is of `Datetime` type and you have records on `2013-07-29` during the day, this query will not fetch them and neither would `BETWEEN`. You should use `< '2013-07-30'` – Nenad Zivkovic Aug 06 '13 at 08:11

1 Answers1

3

(There is no =< operator in SQL, you mean the <= operator.)

The between operator compares the values inclusively, so it's the same as using the <= and >= operators.

If the values are just a date, you can use inclusive values like that. If they also contain a time component, you should consider using an exclusive higher limit. That way you can query different ranges without overlap, e.g. the value 2013-07-30 00:00:00.000 will not be included both in the ranges 2013-07-07 to 2013-07-30 and 2013-07-30 to 2013-08-02. The expression Date < '2013-07-30' will include all values up to 2013-07-29 23:59:59.999 but not 2013-07-30 00:00:00.000.

Example:

Declare @DateTo varchar(max) = '2013-07-30' -- the day after the range
Declare @DateFrom varchar(max) = '2013-07-07'

SELECT * FROM Records WHERE Date < @DateTo AND Date >= @DateFrom
Guffa
  • 687,336
  • 108
  • 737
  • 1,005