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.


Instead of specifying 23:59:59.999 you must specify 23:59:59.997 due to rounding.

The far better way would be to :

WHERE MyDate >= '2013-03-05' AND MyDate < '2013-03-06'

Since this captures ALL activity on 2013-03-05

  • 3
    A full discussion of the behavior and how to handle it is covered here: sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/…
    – Mike Fal
    Mar 6 '13 at 19:05
  • If the range was in our own code that would be what we would do but we are passing to other procs that only take datetime for range and are inclusive on both ends
    – RThomas
    Mar 6 '13 at 19:16
  • 3
    Even better is >= '20130305' AND < '20130306' - this way if someone sets SET LANGUAGE FRENCH;, for example, the query still works as expected. Mar 6 '13 at 19:29
  • 2
    @RThomas so fix the other procedures; they're wrong. Or write your own. What Microsoft procedures take datetime ranges and make them inclusive? This seems too short-sighted even for them. Mar 6 '13 at 19:30
  • 1
    Its turning out to be a "taking their word for it" problem on my end with the vendor proc. They are using the LSN time mapping functions related to change data capture which DO allow < instead of =< calls. So, we'll be fixing code. msdn.microsoft.com/en-us/library/bb500137.aspx
    – RThomas
    Mar 6 '13 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.