datetime bug?

try following:

select convert (datetime, '2004-04-05 3:01:01:002')
--result: '2004-04-05 3:01:01:003'

select dateadd (ms, 1, '2004-04-05 3:01:01:001')
--result: '2004-04-05 3:01:01:000'

currently I am using version 8.00.818
anyone know how to fix this? thanks a million!That's cute. My version is the 859 on Personal and 818 on Enterprise and Standard, and I get this while just doing the CONVERT part of your code:

2004-04-05 03:00:00.910 2004-04-05 03:00:00.910
2004-04-05 03:00:00.911 2004-04-05 03:00:00.910
2004-04-05 03:00:00.912 2004-04-05 03:00:00.913
2004-04-05 03:00:00.913 2004-04-05 03:00:00.913
2004-04-05 03:00:00.914 2004-04-05 03:00:00.913
2004-04-05 03:00:00.915 2004-04-05 03:00:00.917
2004-04-05 03:00:00.916 2004-04-05 03:00:00.917
2004-04-05 03:00:00.917 2004-04-05 03:00:00.917
2004-04-05 03:00:00.918 2004-04-05 03:00:00.917
2004-04-05 03:00:00.919 2004-04-05 03:00:00.920|||i just found this on ms site:
http://support.microsoft.com/default.aspx?scid=kb;en-us;281676&Product=sql2k#appliesto

well, they say it's not a bug, it's 'designed' that way... bs...|||However, this produces the right result:


if cast('2004-04-05 03:00:00.918' as datetime) = dateadd(ms, 1, '2004-04-05 03:00:00.917')
print 'EQ'
else
print 'NEQ'


So I think it's a display formatting issue, not precision bug.|||it's a precision bug, try this:

select datediff (ms, '2004-04-05 3:01:01:005', '2004-04-05 3:01:01:008')|||they are equal because they are both incorrect...

hehe, two wrong answers don't make it right :P|||Man, this is baaaaaaaaaaaaaaaaaaaaaaad...


declare @.d1 datetime, @.d2 datetime
select @.d1='2004-04-05 3:01:01.004', @.d2='2004-04-05 3:01:01.008'
select @.d1, @.d2, datediff(ms, @.d1, @.d2)
if datediff (ms, @.d1, @.d2) = 4
print 'bamboo7 is wrong'
else
print 'bamboo7 is right'|||OK, reported this to MS, see what comes out of it...That's been bugging me since bamboo7's last post...|||You do know that ms are only recorded in .003 increments....

It can't go
.001
.002
.003

It's
.003
.006
.009

EDIT: BOL

datetime and smalldatetime
datetime

Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.|||Oh man, why did I even bother!!!! Should have read BOL before starting this mess...|||That's just bad maths, you shouldn't display decimal places that represent more accuracy than you have provided.|||That's just bad maths, you shouldn't display decimal places that represent more accuracy than you have provided.Huh? I am not trying to display with more accuracy that I have provided (boy, that's a mouthfull)...And where do you see bad math here? As BOL says, - it's 0.003|||I don't mean you, I mean SQL Server. It is displaying to 1000ths of a second when it only supports to a 300th.

I was told that you should only show the number of decimal places that your result is accurate too. Under this logic, Microsoft should only provide 2 decimal places, not 3. Though I suppose this logic doesn't apply so neatly to timing precision.|||Oh, got it, you're right. It would have made much more sense to drop the 1000's precision and just let us live with 0.99