Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2012

    Unanswered: Issue while converting datetimeoffset field to datetime field

    DECLARE @datetimeoffset datetimeoffset(3)
    DECLARE @datetime datetime

    SELECT @datetimeoffset = '2012-11-08T17:22:13.575+00:00'
    SELECT @datetime = @datetimeoffset
    SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
    __________________________________________________ ___________
    Result of above SQL is
    @datetimeoffset datetime
    2012-11-08 17:22:13.575 +00:00 2012-11-08 17:22:13.577
    __________________________________________________ ____________

    The result should be '2012-11-08 17:22:13.575', can anyone please let me know why the milliseconds value is incorrect

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 17
    The datetime datatype only resolves to about 3 miliseconds. I forget the exact reason for this, but I think it dates back to when the OS clock had a similar problem. Try using the datetime2 datatype.

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    MCrowley is correct. The DATETIME datatype does resolve down to roughly 3 ms precision. The DATETIME2 database is more precise. For more details, see: Date and Time Types

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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