Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Nanoseconds


Nanoseconds

Author
Message
Stuart Davies
Stuart Davies
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4485 Visits: 4549
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
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
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16453 Visits: 13207
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 :-D
Apology accepted. Cool



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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10709 Visits: 12008
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
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10709 Visits: 12008
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
sanket kokane
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 1019
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

-----------------------------------------------------------------------------
संकेत कोकणे
kalyani.k478
kalyani.k478
SSChasing Mays
SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)

Group: General Forum Members
Points: 638 Visits: 97
Great Question
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10709 Visits: 12008
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

(Bob Brown)
(Bob Brown)
SSC Eights!
SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)

Group: General Forum Members
Points: 815 Visits: 1145
This is absolutely the worse question I have encountered on QotD. Think I will pass on future Tom questions.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16574 Visits: 17018
(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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lon-860191
Lon-860191
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1887 Visits: 278
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.
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