Basic Date Function

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


    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: 😀

    I noticed it several hours ago but as it just supported my point didn't feel it needed a response.

    The main issue with this question has nothing to do with understanding of the operation of SQL server but rather the failure of the poser or the person selecting them to properly formulate this question. These question have the intent of educating but fail miserably when they highlight other failings although the arguments resulting from it certainly succeed in that respect.

    The problem with this setup that makes it non default is twofold - the client and server that the user is using are set to different timezones from each other and that the country settings of the server does not match the default for that time zone. More importantly these differences affect the result.

    I would not consider it to be a non default installation if both were set to the same timezone as each other and the country settings to match at least one country in that timezone. That that timezone is different from my own generally is irrelevant. Although in this case as my timezone is GMT which is for practical purposes the same as UTC it opens up the possibility that two of the answers will in that particular situation generate the same result.

    Here the answer considered to be correct will only be obtained if you run with the specific settings of client and server being offset from each other by that time difference.

    This is the difference betwwen the arguments

    Joey is a Budgie

    All budgies are birds

    Hence Joey is a bird

    Which is a valid assertion

    and

    Joey is a bird

    All budgies are birds

    Hence Joey is a budgie

    which cannot be assumed from the statements given

    The ability to fully specify the problem is a fundemental requirement for effective coding and as the act of submitting a question to be QOTD implies a level of expertise it is reasonable to expect them to fully describe the environment.

  • crmitchell (2/15/2013)


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


    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: 😀

    I noticed it several hours ago but as it just supported my point didn't feel it needed a response.

    Tom, you were right!

    the client and server that the user is using are set to different timezones from each other and that the country settings of the server does not match the default for that time zone.

    There is nothing in the question that implies any of this.

    That that timezone is different from my own generally is irrelevant.

    You are correct!

    Although in this case as my timezone is GMT which is for practical purposes the same as UTC it opens up the possibility that two of the answers will in that particular situation generate the same result.

    You mean in that particular situation which isn't the one implied by the question...

    Joey is a Budgie

    Yes, I think he probably is.

  • sipas (2/15/2013)


    crmitchell (2/15/2013)


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


    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: 😀

    the client and server that the user is using are set to different timezones from each other and that the country settings of the server does not match the default for that time zone.

    There is nothing in the question that implies any of this.

    .

    And that is the problem. If you think that try carrying out the query at that time on a set of client and server set to any other time offset and see if you get the expected result.

    The date format given is US specific. - Implied by the question

    The time returned by the server being offset implies the two boxes are set to different times - again implied by the question.

    That the date format does not match the default format for those countries offset from the US time zones also implies the last part.

  • crmitchell (2/15/2013)


    The time returned by the server being offset implies the two boxes are set to different times

    But what implies there are two boxes?

    I can tell you're not gong to be convinced(!), but just so others are clear, I think this is what you should take away from the question - to quote BOL:

    SYSDATETIME returns the date and time of the computer on which the instance of SQL Server is running

    SYSUTCDATETIME returns the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time).

    Therefore, on the computer on which the instance of SQL Server is running, if the system date and time is not UTC, SYSUTCDATETIME will return a different date and time to the system date and time. There is only one computer involved.

    A question about two computers set to different timezones is a completely different question, possibly an interesting question, but not this question.

  • +1

    Yeah, timezones.

    There is one time! that is NOW().

  • sipas (2/15/2013)


    But what implies there are two boxes?

    Nothing.

    What suggests there is only one?

    Nothing.

    Either requires an assumption.

  • +1. Nice question.

  • Yet another question that deserves to be removed along with all associated points (or lack thereof), as this is clearly dependent on not only WHEN you answer the question, but WHERE you happen to be in the world at the time. I "incorrectly" assumed that the composer would take that into account, and that therefore, such a bias wouldn't make it into a question, and that therefore the answer SYSDATETIME() was good enough based on format alone. While I "discovered" that SYSUTCDATETIME() also uses the same format, that's hardly useful information, given that I won't be using it for any reason from where I'm located. Please consider the value of the question before deciding it's a good one.

  • sgmunson (3/13/2013)


    Please consider the value of the question before deciding it's a good one.

    Please consider the value of your reply before adding the thousandth identical "this question is wrong" post 😉

  • 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 got it right with time part...

  • why not CURRENT_TIMESTAMP??

    Thanks.

Viewing 11 posts - 46 through 56 (of 56 total)

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