Sql-server – cutoff when using datetime and date datatypes in the where clause

datatypesdatedatetimesql servert-sql

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?

Best Answer

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.

Related Question