Nanoseconds

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Tom Thomson » Nanoseconds

38 posts, Page 1 of 4

1 2 3 4 »»»

Nanoseconds

Rate Topic

Display Mode

Topic Options

Author

Message

L' Eomot Inversé

Posted Wednesday, November 07, 2012 8:57 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 7,112, Visits: 7,188

Comments posted to this topic are about the item Nanoseconds

Tom
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.

Post #1382248

sanket kokane

Posted Wednesday, November 07, 2012 10:22 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 5:04 AM
Points: 323, Visits: 966

I got it wrong ...

you should mention sql server Version in question .

Sysdatetime () not present in sql 2000 and 2005

-----------------------------------------------------------------------------
संकेत कोकणे

Post #1382256

vinu512

Posted Wednesday, November 07, 2012 11:06 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:06 AM
Points: 1,051, Visits: 1,442

--Method 2:
--use the internal structure of datetime(2)
cast(cast(reverse(substring(cast(@t as binary(9)),2,5))
as binary(5)) as bigint)*100

There is a variable being used "@t".....which is nowhere declared.....When you post example code with the questions then you should make sure that the code is correct.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden Wink

Post #1382266

Stewart "Arturius" Campbell

Posted Wednesday, November 07, 2012 11:54 PM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, May 23, 2013 8:31 AM
Points: 3,129, Visits: 4,312

Interesting question, thanks, Tom

While method 2 appears incomplete, it is not difficult to deduce what it should be: given that all other methods refer to sysdatetime(), which is returned as a datetime2 data type, we can presume the actual method should have been

declare @t datetime2 = sysdatetime()
select cast(cast(reverse(substring(cast(@t as binary(9)),2,5))
   as binary(5)) as bigint)*100

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #1382280

Koen Verbeeck

Posted Thursday, November 08, 2012 12:45 AM

SSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 2:11 AM
Points: 9,378, Visits: 6,473

sanket kokane (11/7/2012)

I got it wrong ...

you should mention sql server Version in question .

Sysdatetime () not present in sql 2000 and 2005

So, for which versions do you think the question is meant? Obviously for 2008 and up. There's also a consensus that versions who are no longer supported by Microsoft do not count for Questions of the Day.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1382296

Koen Verbeeck

Posted Thursday, November 08, 2012 12:46 AM

SSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 2:11 AM
Points: 9,378, Visits: 6,473

Nice question Tom, but I doubt if I'll ever use it Smile

(ps: you must be getting old if you can't count to four anymore Tongue

)

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1382298

Carlo Romagnano

Posted Thursday, November 08, 2012 1:11 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:56 AM
Points: 1,972, Visits: 1,822

Boh!!!!!
I do not understand what the question is about, so, how can I answer correctly?
The method 2 is incomplete or bad written.
Cool

I do not like qotd too long.

Post #1382313

derek.colley

Posted Thursday, November 08, 2012 1:44 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:19 AM
Points: 496, Visits: 584

This was a great question and I learned something new.

I do, however have a question of my own. I was under the impression that due to the internal timekeeping processes that SQL Server relies upon, it is unwise to rely on precise timings in the range 0-3ms (0-3000ns)? In other words, when comparing dates or using dates as boundaries, we should bear in mind that the range .997 to 1 is unreliable, due to these timing issues?

There's a Stack Overflow post about this rounding error here -> http://stackoverflow.com/questions/3584850/sql-server-datetime-parameter-rounding-warning and some other references available.

With this knowledge in mind, the question specifies '... which, if any, will work reliably?' My answer would be none - since you are measuring in 1/10ths of a ms (100ns) intervals and the rounding error may invalidate any calculations.

Of course, if this argument doesn't apply to DATETIME2 (rather than DATETIME) then I offer you a hearty apology and withdraw my comments.

Still a great QotD. Thank you.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??! Crazy

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Post #1382322

sqlnaive

Posted Thursday, November 08, 2012 2:40 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:30 AM
Points: 2,663, Visits: 1,680

This was a good question. I gave wrong answer or better to say incomplete answer (opted for only 4). Method 2 I was not sure because of code written was not complete.

from my perspective, losing 2 points is not a big deal than gaining small but important piece of knowledge.

Post #1382348

matthew.flower

Posted Thursday, November 08, 2012 2:44 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 20, 2013 2:16 AM
Points: 555, Visits: 170

While I haven't tried them (have work to do) I am not convinced that any of them "work reliably" when you consider changes to and from daylight savings.

In the UK at least daylight savings changes happen at either 1am which becomes 2am or 2am which becomes 1am, so 2am back to midnight can be 1 hour or 3 hours at least once a year.

Unless I am missing something none of these methods convert both midnight and the current time to UTC before doing the difference between them so I would argue that the correct answer is none of them.

Post #1382351

« Prev Topic | Next Topic »

38 posts, Page 1 of 4

1 2 3 4 »»»

Permissions