Post reply

datepart for milliseconds not precise in SQL Server 2008?

  • March 14, 2011 at 3:51 pm

    #389044

    Why does this:

    DATEPART(MILLISECOND,cast('2011-03-14 17:43:52.134' as datetime))

    return 133?

    and

    DATEPART(MILLISECOND,cast('2011-03-14 17:43:52.135' as datetime))

    return 137?

    Is there an inconsistency in anything smaller than a second in SQL Server 2008?

    Donalith

  • March 14, 2011 at 4:02 pm

    #1298605

    Nevermind.. I didn't realize that SQL Server only maintained a 3.33 precision on milliseconds..

    case closed.. thanks

    Donalith

  • March 14, 2011 at 4:30 pm

    #1298610

    IIRC, the resolution of some of the new datetime function in 2k8 has a much better resolution.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 14, 2011 at 4:43 pm

    #1298616

    Use the TIME datatype instead of DATETIME for higher resolution on timings. It's accurate to 100 nanoseconds.

    Edit: Left the window open in the background too long I see, I've just added some clarity to what Jeff already mentioned. 🙂

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • March 14, 2011 at 7:56 pm

    #1298643

    Also the datetime2 and datetimeoffset data types as well in SQL Server 2008.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply