Is there a cutoff when using datetime and date datatypes in the where clause? 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?
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.