SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Nanoseconds


Nanoseconds

Author
Message
mtassin
mtassin
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7196 Visits: 72521
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
vk-kirov
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4334 Visits: 4408
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)

George M Parker
George M Parker
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1572 Visits: 1472
An incomplete answer that generates a syntax error cannot possibly be a correct answer, therefore only option 4 is correct. :-D

--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
malleswarareddy_m
malleswarareddy_m
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2729 Visits: 1189
I select method 4 only. so I given wrong answer.

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Kick6Tiger
Kick6Tiger
SSC Eights!
SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)

Group: General Forum Members
Points: 891 Visits: 767
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!
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15523 Visits: 25280
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: Administrators
Points: 144552 Visits: 19424
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
My Blog: www.voiceofthedba.com
Matt Marston
Matt Marston
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2045 Visits: 412
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());


Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25773 Visits: 12494
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

call.copse
call.copse
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5576 Visits: 2008
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search