Does anyone know the range comparison of the BETWEEN clause? if I have a datetime datatype, does the BETWEEN clause compare until hour/minute/second level?
views:
72answers:
4
+4
A:
yes it does, if its the same (down to the millasecond) then it is valid and will assert to true. So will be shown
Amjid Qureshi
2010-08-12 02:02:48
Note: While SQL Server's datetime shows time to the thousandths place (millisecond) It only has 1/300 of a second resolution.
Shannon Severance
2010-08-12 03:19:43
+2
A:
It depends on the data type. BETWEEN returns TRUE if the value of the test_expression is greater than or equal to the value of the begin_expression and less than or equal to the value of the end_expression (Source).
The following:
BETWEEN date_field '2010-01-01 12:00:00' AND '2010-02-01 12:00:00'
is equivalent to this:
date_field >= '2010-01-01 12:00:00' AND date_field <= '2010-02-01 12:00:00'
Daniel Vassallo
2010-08-12 02:04:28
Mind that SQL Server will implicitly evaluate the data type based on the data type of `date_field`. If it were string based, the evaluation would be string based. For dates, it won't accept just any date format.
OMG Ponies
2010-08-12 02:10:07
+3
A:
This:
WHERE datetime_column BETWEEN '2010-08-11' AND '2010-08-12'
is equivalent to
WHERE (datetime_column >= '2010-08-11 00:00:00.000' AND datetime_column <= '2010-08-12 00:00:00.000')
There are two things to note here:
- This is true everywhere you use a datetime type. All datetime values include a time portion that's accurate and exact down to about 3 or 4 milliseconds, even if you didn't specify it. Entering a literal like
'2010-08-11'doesn't mean you're checking on an entire day. - The range is inclusive on both ends - you keep the first instant of the last day as well, and so IMO it's not usually a good idea to use between with datetime types. This is especially bad if you have a column that only stores dates with zero values for the time, as you could include an entire extra day beyond what you intended.
Joel Coehoorn
2010-08-12 02:08:40
Re your second point, I often find that often people _exclude_ almost a complete day, when the time components are non-zero for the values held in the column. They'll do a search for everything in July: `... date_column between '2010-07-01' and '2010-07-31'` and not realized they are missing almost all of the records for July 31st. The conclusion is the same, don't use `between` for dates.
Shannon Severance
2010-08-12 03:24:32
@Shannon Severance: My alternative conclusion is to always state the time value (because it always exists) even if it is zero and use the last time granule for end date values (1/300 for SQL Server `DATETIME` values) e.g. `date_column BETWEEN '2010-07-01T00:00:00.000' and '2010-07-31T23:59:59.997'` -- obviously you have to get the SQL DDL correct too and ensure all end dates have a time element `'23:59:59.997'`.
onedaywhen
2010-08-12 07:45:36
@Shannon Severance: the other alternative conclusion to not use closed-closed representation (where the end date value actually falls within the period) and instead use closed-open representation (where the end date value falls outside the period) e.g. the year 2010 would be modelled using a pair of `DATETIME` values `('2010-01-01T00:00:00.000', '2011-01-01T00:00:00.000'}`.
onedaywhen
2010-08-12 07:52:03
@Shannon Severance: ...but what you most often see is closed-closed representation where the smallest temporal granule is assumed to be one day e.g. the year 2010 would be modelled using the pair `('2010-01-01', '2010-12-31'}` without the SQL DDL and SQL DML to ensure `DATETIME` values are always be rounded down to the nearest day, which is a real pain to do so it doesn't get done. In other words, these errors often occur because of sloppy coding at the SQL DDL and stored proc level. Temporal databases are hard to get right, hurray for MSSQL2008's `DATE` and `TIME` types :)
onedaywhen
2010-08-12 08:01:16
@Shannon Severance: forgot to point out the issue with closed-open representation is that you can no longer use `BETWEEN`, you instead need to use e.g. `(date_column >= '2010-07-01T00:00:00.000' AND date_column < '2010-08-01T00:00:00.000')` i.e. two clauses for one search condition. No so nice as using `BETWEEN`, especially when you consider that a start and end date values are subatomic parts of a single period.
onedaywhen
2010-08-12 08:08:27
+1 for the second point. `between` is evil... it try to avoid it as much as possible.
liho1eye
2010-08-12 11:38:05
@onedaywhen: Yes, it is possible to use between down to the last time granule, but that has its own set of typical errors. 1) People who think that SQL Server has millisecond resolution and will use `'2010-07-31T23:59:59.999'` instead of the correct `'2010-07-31T23:59:59.997'`. 2) If you switch between databases, the time granuals are diffent. 3) And now with SQL Server 2008 and `datetime2`, the time granuals can be different for different columns and variables. Closed-open works in all cases. It is a habit, that for me, works across database systems and date/time types.
Shannon Severance
2010-08-12 16:17:09
@Shannon Severance: your points 1, 2 and 3: Indeed but if you have provided 'helper' procs that round to the appropriate time granule then you have done all you can to steer users down the correct path (short of revoking their privileges on the tables :) I agree closed-open comes out on top but I have used closed-closed extensively and it can be made to work well. Thanks for discussion :)
onedaywhen
2010-08-13 07:50:12