datepart for milliseconds not precise in SQL Server 2008?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » datepart for milliseconds not precise in SQL...

datepart for milliseconds not precise in SQL Server 2008?

Rate Topic

Display Mode

Topic Options

Author

Message

Erin Ramsay

Posted Monday, March 14, 2011 3:51 PM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 420, Visits: 774

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

Post #1078032

Erin Ramsay

Posted Monday, March 14, 2011 4:02 PM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 420, Visits: 774

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

case closed.. thanks

Donalith

Post #1078034

Jeff Moden

Posted Monday, March 14, 2011 4:30 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1078042

Evil Kraig F

Posted Monday, March 14, 2011 4:43 PM

SSCertifiable

Group: General Forum Members
Last Login: Saturday, May 18, 2013 10:09 PM
Points: 5,658, Visits: 6,100

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. Smile

- 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1078050

Lynn Pettis

Posted Monday, March 14, 2011 7:56 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:02 AM
Points: 21,596, Visits: 27,415

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

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1078086

« Prev Topic | Next Topic »

Permissions