Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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.

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
–
RThomasMar 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.
–
Aaron Bertrand♦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.
–
Aaron Bertrand♦Mar 6 '13 at 19:30

@RThomas - if you don't want to rewrite anything, just use the 23:59:59.997 and live with it...?
–
Max VernonMar 6 '13 at 20:08