Results 1 to 14 of 14

Thread: datetime bug?

  1. #1
    Join Date
    Jan 2004
    Posts
    8

    Angry Unanswered: 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!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jan 2004
    Posts
    8
    i just found this on ms site:
    http://support.microsoft.com/default...ql2k#appliesto

    well, they say it's not a bug, it's 'designed' that way... bs...

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jan 2004
    Posts
    8
    it's a precision bug, try this:

    select datediff (ms, '2004-04-05 3:01:01:005', '2004-04-05 3:01:01:008')

  6. #6
    Join Date
    Jan 2004
    Posts
    8
    they are equal because they are both incorrect...

    hehe, two wrong answers don't make it right :P

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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'
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    OK, reported this to MS, see what comes out of it...That's been bugging me since bamboo7's last post...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Oh man, why did I even bother!!!! Should have read BOL before starting this mess...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jul 2004
    Location
    New Zealand
    Posts
    9
    That's just bad maths, you shouldn't display decimal places that represent more accuracy than you have provided.

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by alpha_foobar
    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
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Jul 2004
    Location
    New Zealand
    Posts
    9
    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.

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •