November 8, 2012 at 5:21 am
Great Question
November 8, 2012 at 5:22 am
Koen Verbeeck (11/8/2012)
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)
I am indeed getting old (although I can't seem to catch up with Ron), but I can still count four: zero, one, two, three :-D. It's just that I sometimes forget to add one when I've counted something. :laugh:
Tom
November 8, 2012 at 6:06 am
This is absolutely the worse question I have encountered on QotD. Think I will pass on future Tom questions.
November 8, 2012 at 7:30 am
(Bob Brown) (11/8/2012)
This is absolutely the worse question I have encountered on QotD. Think I will pass on future Tom questions.
If this is the worst QotD you have encountered, I would encourage you to peruse more QotD.
Nice question Tom. I got tripped up by method 2 and answered only 4 but as always I learned something new.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2012 at 7:48 am
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.
November 8, 2012 at 7:50 am
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.
November 8, 2012 at 8:10 am
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)
November 8, 2012 at 8:15 am
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
November 8, 2012 at 8:15 am
I select method 4 only. so I given wrong answer.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
November 8, 2012 at 9:56 am
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!
November 8, 2012 at 10:03 am
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.
November 8, 2012 at 10:04 am
Typos corrected. I'll award back points to this time.
November 8, 2012 at 10:33 am
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());
November 8, 2012 at 12:37 pm
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
November 9, 2012 at 3:44 am
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.
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply