Is there a cutoff when using datetime and date datatypes in the where clause?
Stop using BETWEEN for date range queries; it's ambiguous and brittle. WHERE COL &gt;= '20160901' and COL &lt; '20161001' is so much easier to construct and isn't vulnerable to unexpected changes in type / precision / rounding. 

See this post and this post.