BETWEEN DateTime

1

I was trying to query a week of data that had a DateTime column. I used a where clause like this

WHERE [BEGIN_TIME] between CAST('2016-01-31 00:00' AS DATE) and CAST('2016-02-6 23:59' AS DATE)
and/or
WHERE [BEGIN_TIME] between '2016-01-31 00:00' and '2016-02-6 23:59'

and it would not return any rows for the 6th even though I knew there were 22 that should be returned.

I thought I had done something like this in the past. Shouldn't this have worked?

When I changed it to (notice I went to the 7th)

WHERE [BEGIN_TIME] between '2016-01-31' AND '2016-02-7'

The query returned rows for the 6th. All rows, whether on the 6th or on other dates do have a time component that is not 00:00:00. In other words they are all like 2016-02-01 08:30:00.000

Greg

2

don't use BETWEEN with time components. If BEGIN_TIME is datetime, that is. The safest way is:

WHERE time_col >= begin_date and time_col < end_date

3

I'm sure its obvious from GBritton's post, but if not then "end_date" is the "day after the final day to be included"

4

Thanks. I will use that format in the future. I wonder how may queries I've written that have been wrong.

Greg

5

From your description of the problem it seems like your column BEGIN_TIME is a datetime. It's the CAST of the 6th to a date that is causing you to miss those items. The CAST removes the time portion. The effect is that when it compares to BEGIN_TIME it gets implicitly CAST to a datetime with a time portion of midnight (start of day). Since it does this, any BEGIN_TIME on the 6th with any time past midnight falls outside the window you defined.
I'll throw my two cents in on how/whether to use BETWEEN for datetime values. If you simply use midnight of the following day (Feb 7th) there is the possibility that there are BEGIN_TIME values with that exact time. You may or may not care but your stated criteria is that you only want items from the 31st through the 6th. I do like to use the BETWEEN operator; it can be significantly more efficient. It does suffer from some potential edge case issues though. Even your end time leaves out the last minute of the day for the 6th; it doesn't cover the last 60 seconds of the day. Again, you may feel that your data would never fall into this sliver of time but the possibility does exist. If you had "nice" data, you could simply not CAST your datetime constants to DATE and you'd get the results you expect. If your data wasn't so "nice" you could define your end point as CAST('20160206 23:59:59.997' AS DATETIME). If you change to datetime2 as the datatype, you'll need to extend the sub-second value to .9999999

1 Like
6

And if it isn't clear from this - the end_date either does not have a time component or the time component is 00:00:00.000.

7

Thanks for the input.

select CAST('2016-02-6 23:59' AS DATETIME) = 2016-02-06 23:59:00.000

select CAST('2016-02-6 23:59' AS DATE) = 2016-02-06

My 'normal' way of doing this in the past would have been...

WHERE [BEGIN_TIME] between '2016-01-31 00:00' and '2016-02-6 23:59'

Without casting. When this failed I changed to CAST, but cast it to the wrong data type.

Greg

8

I've made that mistake too, Now I use the >= starddate and < enddate + 1 style and it always works

1 Like
9

Won't

MyColumn BETWEEN @Start AND @End

give you the same query plan as

MyColumn >= @Start AND MyColumn < @EndPlusOne

?

Then there are the milliseconds as you go on to mention ... and the fact that you cannot have .999 of a second (represented in a DATETIME) so you have to actually know the correct, final, millisecond value for the day, when stored as DATETIME precision, if you are going to use BETWEEN on a DATETIME. And then there is the risk that the column is (or is later changed to become) a DATETIME2 with its greater accuracy for the endpoint.

Personally I think that the best way of avoiding the edge-conditions is to get used to using "Less than day-after-endpoint" as the top limit condition.

Hehehe ... Good Point, thanks. Scary what comes naturally after all these years, yet a newcomer would need to be shown, the first time.

10

You may need this read this post https://madhivanan.wordpress.com/2015/12/21/do-not-use-between-operator-on-datetime-comparison/