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 1234»»»

Nanoseconds Expand / Collapse
Author
Message
Posted Wednesday, November 07, 2012 8:57 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 8,296, Visits: 8,750
Comments posted to this topic are about the item Nanoseconds

Tom
Post #1382248
Posted Wednesday, November 07, 2012 10:22 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 01, 2013 10:17 AM
Points: 323, Visits: 984
I got it wrong ...

you should mention sql server Version in question .

Sysdatetime () not present in sql 2000 and 2005


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1382256
Posted Wednesday, November 07, 2012 11:06 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573

--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
Post #1382266
Posted Wednesday, November 07, 2012 11:54 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 3,694, Visits: 4,827
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
Posted Thursday, November 08, 2012 12:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 12,228, Visits: 9,204
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
Posted Thursday, November 08, 2012 12:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 12,228, Visits: 9,204
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 )




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
Posted Thursday, November 08, 2012 1:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:39 AM
Points: 2,397, Visits: 2,287
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.
Post #1382313
Posted Thursday, November 08, 2012 1:44 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
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??!

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
Posted Thursday, November 08, 2012 2:40 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:04 AM
Points: 3,517, Visits: 2,606
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
Posted Thursday, November 08, 2012 2:44 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:41 AM
Points: 690, Visits: 218
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 »

Add to briefcase 1234»»»

Permissions Expand / Collapse