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 Thursday, November 8, 2012 7:50 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: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
I was going to say... method two.

cast(cast(reverse(substring(cast(@t as binary(9)),2,5))
as binary(5)) as bigint)*100

Lacks a select, lacks a declare for @t, etc... and the question was , which methods will give us the nanoseconds since midnight. Hard to tell if the statement was or wasn't intentionally properly written.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1382507
Posted Thursday, November 8, 2012 8:10 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: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
Lon-860191 (11/8/2012)
Good question, but I would question if a method is reliable if it does not work less than 1/3 of the time daily, method 2 overloaded at 8 am since this means that it would be only good during after midnight but only through early morning.

Can you prove this by posting an example of T-SQL code?
It seems to me that the method works fine even for the last 100 nanoseconds of the day:

declare @t datetime2;
set @t = '2012-11-08T23:59:59.9999999';
select @t, cast(cast(reverse(substring(cast(@t as binary(9)),2,5)) as binary(5)) as bigint)*100;

                       
---------------------- --------------------
2012-11-08 23:59:59.99 86399999999900

(1 row(s) affected)
Post #1382518
Posted Thursday, November 8, 2012 8:15 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 8:15 AM
Points: 652, Visits: 1,428
An incomplete answer that generates a syntax error cannot possibly be a correct answer, therefore only option 4 is correct.

--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
Post #1382520
Posted Thursday, November 8, 2012 8:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,939, Visits: 1,162
I select method 4 only. so I given wrong answer.

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #1382521
Posted Thursday, November 8, 2012 9:56 AM


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: Today @ 9:55 AM
Points: 594, Visits: 660
I guess #4 only because it was a long winded answer. After trying to run the options, I have to agree that #2 should not be a valid answer until the typo is fixed. After all, some typos are intentional on here.

Good to know in case I ever build those time-traveling nanorobots.


Aigle de Guerre!
Post #1382598
Posted Thursday, November 8, 2012 10:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
L' Eomot Inversé
I am indeed getting old (although I can't seem to catch up with Ron),


Seems like I will have to invoke DBCC TIMEWARP:
http://www.sqlservercentral.com/Forums/Topic860953-61-1.aspx

But I am having this problem
those parameters are not optional, the 137 parameters are required parameters - and from what I recall the next 255 are optional parameters that can be used to modify how the 137 required parameters are interpreted


I will soon figure those parameters out and invoke same so that you can catch up with me.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1382602
Posted Thursday, November 8, 2012 10:04 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 10:22 AM
Points: 31,214, Visits: 15,658
Typos corrected. I'll award back points to this time.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1382603
Posted Thursday, November 8, 2012 10:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 27, 2014 12:38 PM
Points: 1,921, Visits: 400
If you ever did need to do this and you are uncomfortable depending on the binary format and the complex SELECT in option 4 scares you (it does me). Here is another approach making use of DATEPART:

SELECT
(
(
-- hours * 60 + minutes = total minutes;
DATEPART(hour, SYSDATETIME()) * 60 + DATEPART(minute, SYSDATETIME())
-- total minutes * 60 + seconds = total seconds;
) * 60 + DATEPART(second, SYSDATETIME())
-- total seconds * 1,000,000,000 + nanoseconds = total nanoseconds;
) * CONVERT(bigint, 1000000000) + DATEPART(nanosecond, SYSDATETIME());

Post #1382619
Posted Thursday, November 8, 2012 12:37 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:10 PM
Points: 7,814, Visits: 9,563
Lon-860191 (11/8/2012)
Good question, but I would question if a method is reliable if it does not work less than 1/3 of the time daily, method 2 overloaded at 8 am since this means that it would be only good during after midnight but only through early morning.

Can you tell us what you did to make method 2 overload at 8am? I can't get it to overload, it works for me right up to 22:59:59.9999999.


Tom
Post #1382684
Posted Friday, November 9, 2012 3:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 1,749, Visits: 1,150
I'll just weigh in here to say it was a fair question from my perspective because it asked about the METHOD, and the intent of this was clear, if not the exact syntax. Also because I got it right this time.
Post #1382923
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse