Basic Date Function

  • Michael Riemer (2/13/2013)


    +1 to the above points.

    Great question as far as idea and content goes, but needs clarification as to where you are in the world!!

    Actually, no it doesn't.

    The only way a sytem-date function in SQL Server will return an hour that is different from the host computer's date/time functions is if the OS's time zone setting is not set to UTC/GMT, and the function used is one of the two that returns UTC (SYSUTCDATETIME() and GETUTCDATE()). So you can figure out the answer with no further information.

    As for the "My computer's located in X", the physical location of the system is totally unrelated to the OS time zone setting. Furthermore, it is possible for the time zone setting to be changed without your knowledge. This situation can happen if a broken or intentionally malicious piece of software has changed your system's time zone setting, or if someone has played a prank on you. You may not expect your computer to behave in this way, but it's perfectly plausible.

    So the question as written needs no further clarification.

  • Koen Verbeeck (2/13/2013)


    This question could've been better if the question asker realized there are more than one countries in the world.

    It looks to me as though a lot of the ANSWERERS need to realize that. The data points could have been:

    Date: 05/17/2012

    Time: 5:10 PM

    Returning: 2012-05-17 18:10:42.8078994

    And we'd still have the UTC function as the only possible right answer of the four given. Any inference that you were in the UK running under BST would be irrelevant.

    This QOD is solid in simply telling you two things, your current time and the result of a query. The only assumption, and a perfectly reasonable one, is that the computers involved are set to their current local time.

    With those three pieces of information, which function was used? The time offset forces us to say the UTC function. The further implication of the 330 minute offset that the computers are in India is interesting, but is not relevant to knowing that SYSUTCDATETIME () is the answer.

  • Hugo Kornelis (2/13/2013)


    I had almost missed the different time in the returned output, as I was focusing completely on the data type. Caught is just in time!

    I do not agree with some of the comments here that time zone of the computer was required to answer this. Once I saw that the time portion of the result was not the same as the listed system time, there was only one option left anyway.

    +1

    I didn't even bother doing the conversion once I saw that the times were different. A good question about a function I never use. Thanks!

  • Hugo Kornelis (2/13/2013)[/b]


    I do not agree with some of the comments here that time zone of the computer was required to answer this. Once I saw that the time portion of the result was not the same as the listed system time, there was only one option left anyway.

    +1

    Even before anwering the question, I realized there is going to be a lot of discussion regarding the time zone differences for this Qotd...

    I too agree that once the given system time and the time in the query result are not same and since there is no offset in the answer, only possible function that could return the answer is sysutcdatettime..

    If we really want to nit pick, then probably the question could be phrased like

    Which one of the functions below could possibly give the following result? Other than that its a good Qotd.

    ___________________________________________________________________
    If I can answer a question then anyone can answer it..trying to reverse the logic.. :hehe:

  • Michael Riemer (2/13/2013)


    +1 to the above points.

    Great question as far as idea and content goes, but needs clarification as to where you are in the world!!

    +1

    (plus one to this plus one)

    😉

  • Revenant (2/13/2013)


    Michael Riemer (2/13/2013)


    +1 to the above points.

    Great question as far as idea and content goes, but needs clarification as to where you are in the world!!

    +1

    (plus one to this plus one)

    😉

    Why do you want clarification as to where you are? With the terms as stated, and the assumption that the clocks are set reasonably accurately, what location information would lead you to a different answer to this QOD?

  • john.arnott (2/13/2013)


    Revenant (2/13/2013)


    Michael Riemer (2/13/2013)


    +1 to the above points.

    Great question as far as idea and content goes, but needs clarification as to where you are in the world!!

    +1

    (plus one to this plus one)

    😉

    Why do you want clarification as to where you are? With the terms as stated, and the assumption that the clocks are set reasonably accurately, what location information would lead you to a different answer to this QOD?

    Thanks to everyone chiming in and clearing up my questions, I have now learnt new functionality for SQL server - my only problem is remembering it.

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]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

  • john.arnott (2/13/2013)


    The only assumption, and a perfectly reasonable one, is that the computers involved are set to their current local time.

    The other assumption is that SQLServer is running on your local machine, or another machine in the same time zone.

    If the current time on my computer is 17:10, and the current time on the computer where SQLServer is installed is 11:40, then sysdatetime() would give the specified results.

  • Started to look at this yesterday and got side tracked. I was reading on the dates and formats and realized there was something in the current project I needed to attend to. So not only did I learn something but it was appropriate to what I was doing as I read the question and researched the functionality.

    Thanks and yes +1

    Not all gray hairs are Dinosaurs!

  • Toreador (2/14/2013)


    john.arnott (2/13/2013)


    The only assumption, and a perfectly reasonable one, is that the computers involved are set to their current local time.

    The other assumption is that SQLServer is running on your local machine, or another machine in the same time zone.

    If the current time on my computer is 17:10, and the current time on the computer where SQLServer is installed is 11:40, then sysdatetime() would give the specified results.

    Aha! Thank you for letting me out of my self-imposed box.

    Side note: This is the sort of thing that justifies the policy in my shop of stating and gaining confirmation for assumptions, even if they seem obvious at first.

  • john.arnott (2/13/2013)


    With those three pieces of information, which function was used? The time offset forces us to say the UTC function. The further implication of the 330 minute offset that the computers are in India is interesting, but is not relevant to knowing that SYSUTCDATETIME () is the answer.

    Actually there is no implication that teh computers are in India; they could be in Sri Lanka.

    Tom

  • Koen Verbeeck (2/13/2013)


    This question could've been better if the question asker realized there are more than one countries in the world.

    Actually, all we need to know is that only two of the four functions suggested will ever produce data in that format and of those two only one will ever produce data different from the local time. We don't need to know anything about the location. If we did, we could narrow it down to 2 countries from the data provided, but not to 1 - maybe whoever next sets a question like this could use UTC+05:45 just so that we could narrow it to 1 country.

    Tom

  • L' Eomot Inversé (2/14/2013)


    Koen Verbeeck (2/13/2013)


    This question could've been better if the question asker realized there are more than one countries in the world.

    Actually, all we need to know is that only two of the four functions suggested will ever produce data in that format and of those two only one will ever produce data different from the local time. We don't need to know anything about the location. If we did, we could narrow it down to 2 countries from the data provided, but not to 1 - maybe whoever next sets a question like this could use UTC+05:45 just so that we could narrow it to 1 country.

    You do need to know about the implications of the locations as the question asked was what WILL generate the desired result not what MAY do so. For myself and the majority of other users none of them WILL do so. It is only in the case of specific non default setup that any of the answers given MAY generate the given result.

    If the answer to the question depends on some specific setup then that setup needs to be defined in the question.

  • crmitchell (2/15/2013)


    L' Eomot Inversé (2/14/2013)


    Koen Verbeeck (2/13/2013)


    This question could've been better if the question asker realized there are more than one countries in the world.

    Actually, all we need to know is that only two of the four functions suggested will ever produce data in that format and of those two only one will ever produce data different from the local time. We don't need to know anything about the location. If we did, we could narrow it down to 2 countries from the data provided, but not to 1 - maybe whoever next sets a question like this could use UTC+05:45 just so that we could narrow it to 1 country.

    You do need to know about the implications of the locations as the question asked was what WILL generate the desired result not what MAY do so. For myself and the majority of other users none of them WILL do so. It is only in the case of specific non default setup that any of the answers given MAY generate the given result.

    If the answer to the question depends on some specific setup then that setup needs to be defined in the question.

    The question says YOUR computer shows the following system date and time - well MY computer doesn't show that date and time so the question must be wrong - how can I answer it if I have to imagine a set of circumstances that don't precisely apply to my own situation?

    The 'specific non default setup' of someone being in a different time zone to myself is just too much for me to contemplate.:w00t:

  • sipas (2/15/2013)


    crmitchell (2/15/2013)


    L' Eomot Inversé (2/14/2013)


    Koen Verbeeck (2/13/2013)


    This question could've been better if the question asker realized there are more than one countries in the world.

    Actually, all we need to know is that only two of the four functions suggested will ever produce data in that format and of those two only one will ever produce data different from the local time. We don't need to know anything about the location. If we did, we could narrow it down to 2 countries from the data provided, but not to 1 - maybe whoever next sets a question like this could use UTC+05:45 just so that we could narrow it to 1 country.

    You do need to know about the implications of the locations as the question asked was what WILL generate the desired result not what MAY do so. For myself and the majority of other users none of them WILL do so. It is only in the case of specific non default setup that any of the answers given MAY generate the given result.

    If the answer to the question depends on some specific setup then that setup needs to be defined in the question.

    The question says YOUR computer shows the following system date and time - well MY computer doesn't show that date and time so the question must be wrong - how can I answer it if I have to imagine a set of circumstances that don't precisely apply to my own situation?

    The 'specific non default setup' of someone being in a different time zone to myself is just too much for me to contemplate.:w00t:

    What worries me there is that - even with the very appropriate emoticon - the person to whom you replied may be insufficiently awake to detect your sarcasm. But I still wish I could have gotten in first with a response like yours :exclamationmark: 😀

    Tom

Viewing 15 posts - 31 through 45 (of 55 total)

You must be logged in to reply to this topic. Login to reply