Wanting to get all records from a single day. So everything between 2013-03-05 00:00:00.000
and 2013-03-05 23:59:59.999
. We're getting overlap at the backend with things that happened at 2013-03-06 00:00:00.000
.
So, in testing I run in SSMS:
select cast('2013-03-05 23:59:59.999' as datetime)
select cast('2013-03-05 23:59:59.996' as datetime)
select cast('2013-03-05 23:59:59.994' as datetime)
And get:
2013-03-06 00:00:00.000
2013-03-05 23:59:59.997
2013-03-05 23:59:59.993
The millisecond part comes back off by a little. In the case of the first line it really matters. Because I don't want 2013-03-06 00:00:00.000 – I want a millisecond before midnight.
TSQL states in documentation that it uses fractional seconds as opposed to .Net datetime which uses milliseconds. This would seem to be a syntax difference only but if you really care about millisecond precision in TSQL are you forced to use DateTime2
across the board. Casting from datetime2
to datetime
still mucks things up by a couple milliseconds.
In c# DateTime.Parse("03/5/2013 23:59:59.999").Millisecond
still returns 999.
Am I interpreting this all right? If I care about millisecond precision am I stuck with datetime2
only.
Normally that would be an easy fix but in this case we're using a Microsoft system function that receives datetime
as parameter.
Best Answer
Instead of specifying 23:59:59.999 you must specify 23:59:59.997 due to rounding.
The far better way would be to :
Since this captures ALL activity on 2013-03-05