Nanoseconds

Question

Post reply

Nanoseconds

TomThomson

SSC Guru

Points: 104773
More actions
November 7, 2012 at 8:57 pm

#264492

Comments posted to this topic are about the item Nanoseconds
Tom
November 7, 2012 at 11:54 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 37 total)

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

sanket kokane Hall of Fame Points: 3465 More actions · Answer 1

I got it wrong ...

you should mention sql server Version in question .

Sysdatetime () not present in sql 2000 and 2005

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

vinu512 SSCoach Points: 15729 More actions · Answer 2

--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[/url] 😉

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

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

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

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Carlo Romagnano SSC-Insane Points: 22679 More actions · Answer 5

Boh!!!!!

I do not understand what the question is about, so, how can I answer correctly?

The method 2 is incomplete or bad written.

😎

I do not like qotd too long.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 6

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 https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

sqlnaive SSCoach Points: 17437 More actions · Answer 7

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.

matthew.flower Default port Points: 1414 More actions · Answer 8

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.

Stuart Davies SSCoach Points: 19505 More actions · Answer 9

sqlnaive (11/8/2012)
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.

+ 1

Still a good question though - maybe I took the supplied options a little bit too literaly

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 10

derek.colley (11/8/2012)
...
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.

It doesn't apply to DATETIME2, only to DATETIME 😀

Apology accepted. 😎

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

TomThomson SSC Guru Points: 104773 More actions · Answer 11

My apologies to all for the incompleteness of option 2. Although I agree with "Arturius" though that it is pretty obvious what is missing and don't think it should have confused anyone, I'll ask Steve to correct it.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 12

matthew.flower (11/8/2012)
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.

You are absolutely correct. The wording needs to be changed to say "except on the days when daylight saving time begins or ends".

Tom

sanket kokane Hall of Fame Points: 3465 More actions · Answer 13

Koen Verbeeck (11/8/2012)
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.

ohhh sorry .. I still have Bunch of servers running on 2000 🙂

I will must say its nice question .

Got to learn something new

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