End of Date Microsoft SQL Bug

if you have to enter the last moment in a day/month, note that 23:59:59.997 is the last moment within a day for Microsoft SQL.  The  23:59:59.998 and 23:59:59.999 milliseconds are typically dropped into the following date if we are doing a count(date).  a Known bug of Microsoft SQL, so if you encounter this, you do need to take these records into account.

So, the following causes some issues:

declare @startDate;
declare @endDate;

set @startDate = ‘2019-01-01 00:00:00.000″
set @endDate = ‘2019-12-31 23:59:59.999″

select count(date)
From transactions_table
where date between @startDate and @endDate

to correct this, make sure the @endDate = ‘2019-12-31 23:59:59.997’  (or whatever date value you choose but the time parameter ends with .997, otherwise it wraps into the following day.