Take the 2-minute tour ×
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.

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.

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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

share|improve this answer
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. –  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 Vernon Mar 6 '13 at 20:08
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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