Is there a cutoff when using datetime and date datatypes in the where clause?

Question

I have a column titled achieved_date. In my query most of the values for that column are 2016-09-30 23:59:59.997.

I then have a where clause that reads as follows:

WHERE achieved_date between '20160901' and '20160930'

The records with the 2016-09-30 23:59:59.997 value are not being included in my results when they should be. I mean the transaction was recorded on the 30th of September.

Any ideas if this is a odd datatype issue?

Answer 1

Stop using BETWEEN for date range queries; it's ambiguous and brittle. WHERE COL >= '20160901' and COL < '20161001' is so much easier to construct and isn't vulnerable to unexpected changes in type / precision / rounding.

See this post and this post.

Answer 2

I have to agree with Aaron Bertrand here. Using 'between' in this situation introduces a certain amount of ambiguity when reading your code. Using more specific operators =,>=,<= etc, will provide more readable code and reliable results.
I also agree with SQLDataInTraining in that the implicit conversions involved are actually causing your results to not return the expected records. Another option though is to cast the DB value as opposed to the literal dates values.

So instead of

WHERE achieved_date > CAST('20160901' AS DATE) 
 AND achieved_date <= CAST('20160930' AS DATE)

try this

WHERE CAST(achieved_date AS DATE) >= '20160901' 
  AND CAST(achieved_date AS DATE) <= '20160930'

This is of course assuming you want all records from both 20160901 and 20160930. If not, adjust the operators accordingly.