Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

datepart for milliseconds not precise in SQL Server 2008? Expand / Collapse
Author
Message
Posted Monday, March 14, 2011 3:51 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:58 AM
Points: 546, Visits: 1,061
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
Posted Monday, March 14, 2011 4:02 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:58 AM
Points: 546, Visits: 1,061
Nevermind.. I didn't realize that SQL Server only maintained a 3.33 precision on milliseconds..

case closed.. thanks

Donalith
Post #1078034
Posted Monday, March 14, 2011 4:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 35,371, Visits: 31,913
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1078042
Posted Monday, March 14, 2011 4:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:08 PM
Points: 5,401, Visits: 7,514
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1078050
Posted Monday, March 14, 2011 7:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 20,739, Visits: 32,523
Also the datetime2 and datetimeoffset data types as well in SQL Server 2008.


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 »

Add to briefcase

Permissions Expand / Collapse