February 13, 2013 at 5:31 am
I got it correct, Not because i'm staying in India. As Hugo and others said, In the given options SYSUTCDATETIME ( ) is the only option which will return such a calculated difference in time.
--
Dineshbabu
Desire to learn new things..
February 13, 2013 at 5:36 am
Dineshbabu (2/13/2013)
I got it correct, Not because i'm staying in India. As Hugo and others said, In the given options SYSUTCDATETIME ( ) is the only option which will return such a calculated difference in time.
Unless the user's computer is in a different time zone from the computer with SQLServer on it 🙂
February 13, 2013 at 5:59 am
Toreador (2/13/2013)
Dineshbabu (2/13/2013)
I got it correct, Not because i'm staying in India. As Hugo and others said, In the given options SYSUTCDATETIME ( ) is the only option which will return such a calculated difference in time.Unless the user's computer is in a different time zone from the computer with SQLServer on it 🙂
Which if you assume default installations would be the case here.
Date format for India is dmy but the questioner asks what would happen if the date is shown as mdy i.e. US format.
From that I would assume server is in India with the client in the US. Not necessarily an uncommon setup but hardly typical. Either that or someone is using a US copy of Windows in India or has their PC set up with a non default installation.
February 13, 2013 at 6:02 am
crmitchell (2/13/2013)
Date format for India is dmy but the questioner asks what would happen if the date is shown as mdy i.e. US format.
Well, the date in the question is clearly not possible to be mistaken for dmy, unless there is a place in the world where they have 17 months or more in the year. The return value from the system call, however, is in ymd format, which is the international standard, so I'm not sure the location *would* actually make a difference--certainly, on my British English installation of SQL server 2012, it still returns ymd format for a SYSUTCDATETIME() call.
February 13, 2013 at 6:20 am
sipas (2/13/2013)
How can there be so many wrong answers??SYSDATETIME would just give you current time, there's no offset displayed, and CURRENT_TIMESTAMP doesn't give enough fractional seconds, so there's only one possible answer.
It doesn't matter what time zone you are actually currently in, for the purposes of this question it is implied that you are in UTC+05:30.
I thought it was a nice, simple, informative question, so thank you.
OK - I wasn't sure of the all the different formats off the top of my head (I answer QOTD without running the code to try and test my self) so took an educated but wrong guess.
I then ran the code (at 13:11 on 13th Feb) and got the following:-
SYSDATETIME ()= 2013-02-13 13:11:37.0611535
,SYSDATETIMEOFFSET ( )=2013-02-13 13:11:37.0611535 +00:00
,SYSUTCDATETIME ( ) = 2013-02-13 13:11:37.0611535
,CURRENT_TIMESTAMP= 2013-02-13 13:11:37.057
From this SYSDATETIMEOFFSET and CURRENT_TIMESTAMP can be removed as options as the format is wrong. Question though - how do you know which is the right answer when two of the answers are the same?
I may, of course, be thick, blind or just not looking hard enough.
Can someone please explain?
-------------------------------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
February 13, 2013 at 6:25 am
Stuart Davies (2/13/2013)
From this SYSDATETIMEOFFSET and CURRENT_TIMESTAMP can be removed as options as the format is wrong. Question though - how do you know which is the right answer when two of the answers are the same?
The time is different in the answer, and only SYSUTCDATETIME() will return an answer that's got a different time to the server's current time zone. (Didn't spot that myself and picked the wrong answer, as I said above).
February 13, 2013 at 6:29 am
Stuart Davies (2/13/2013)
sipas (2/13/2013)
Question though - how do you know which is the right answer when two of the answers are the same?
If SQLServer is running on your own computer, then SYSUTCDATETIME is the only one that could give you a different time from the one shown on your computer. This is presumably the setup that was assumed by the question.
If SQLServer is running on a different computer, then there is no way of knowing. The time shown on your computer is irrelevant to what is returned from SQLServer.
February 13, 2013 at 6:38 am
Toreador (2/13/2013)
Stuart Davies (2/13/2013)
sipas (2/13/2013)
Question though - how do you know which is the right answer when two of the answers are the same?If SQLServer is running on your own computer, then SYSUTCDATETIME is the only one that could give you a different time from the one shown on your computer. This is presumably the setup that was assumed by the question.
If SQLServer is running on a different computer, then there is no way of knowing. The time shown on your computer is irrelevant to what is returned from SQLServer.
Bit of a bummer when the server and client are both in the UK then :ermm:
-------------------------------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
February 13, 2013 at 6:56 am
davoscollective (2/12/2013)
Your computer shows the following system date and time:
If my computer showed that time I would be concerned that it's in US format and not Australian (UK) format! :w00t:
And none of those functions would show a time 5 hours and 30 minutes earlier on my computer, because I am in Sydney and it's currently UTC+11 here during daylight savings, and UTC+10 the rest of the year. 😛
If I were in the Indian Standard Timezone (UTC+0530) then sysutcdatetime() function would be the answer.
Interesting question, I guess if you had mentioned your timezone in the question it might have made the time component more obvious and the question too easy.
I'm all over this one. SYSDATETIME() is just as valid because my computer is in UTC -6 which means that SYSUTCDATETIME() would show 2013-01-17 23:10:00.000000.
February 13, 2013 at 7:06 am
sipas (2/13/2013)
It doesn't matter what time zone you are actually currently in, for the purposes of this question it is implied that you are in UTC+05:30.
Unless you know the person asking this question is looking for UTC, then that may be true. Implied has nothing to do with it. Too many questions have either typos or intentional inaccuracies to trip people up. Due to those two issues many people will answer incorrectly.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 13, 2013 at 7:23 am
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.
I must disagree... depending on the time of day both SYSDATETIME() and SYSUTCDATETIME() will return that value. It just depends on when you run them.
Since there's only 5 time zones out of about 40 that have 30 minute offsets, it comes down to when you notice the values aren't the same.
If the question had included the timezone that I was supposed to be in, then SYSUTCDATETIME() would be obviously correct.
It's like giving somebody a question like
"You have 10 chocolate bars, sue asks for 3, how many do you have left?"
The answer
10, because you don't like Sue and you told her to go beg somewhere else.
February 13, 2013 at 7:25 am
crmitchell (2/13/2013)
Because the question requires the user to make assuptions about how the end user's systems are configured which are defined in the question.In this case we don't know and have to infer
what the timezone of the users PC is set to
What language settings are set on the user's PC - specifically date formats
what the timezone of the user's server with SQL server installed is set to
What language settings are set on the user's server with SQL server installed - specifically date formats
What language settings are set on the SQL server instance.
If these differ from the questioner's setup then the correct answer is probably 'None of the above' which is not an option.
Any question needs to ensure all information to answer it is provided and not need the answerer to make assumptions.
In this case that could have been as simple as saying a default installation of MS Windows and SQL server for whichever region has that timezone (its not the only one with fractional hour offsets though).
+1 I so completely agree
February 13, 2013 at 7:29 am
Example of SYSUTCDATETIME() is 2007-04-30 20:10:02.0474381
So in our scenario [Date: 1/17/2013 Time 5:10 PM]
it should be 2013-01-17 17:10:42.8078994
Check the differences in the below link,
http://msdn.microsoft.com/en-us/library/bb630353.aspx
Please correct me if I am wrong !!
February 13, 2013 at 7:40 am
Toreador (2/13/2013)
Stuart Davies (2/13/2013)
sipas (2/13/2013)
Question though - how do you know which is the right answer when two of the answers are the same?If SQLServer is running on your own computer, then SYSUTCDATETIME is the only one that could give you a different time from the one shown on your computer. This is presumably the setup that was assumed by the question.
If SQLServer is running on a different computer, then there is no way of knowing. The time shown on your computer is irrelevant to what is returned from SQLServer.
^^^ This gets to the crux of the matter. The question says "your computer shows the following system date and time", but all four functions base their return values on the system date and time of the computer on which the SQL Server instance is running. The question does not specify that "your computer" and the computer on which the SQL Server instance is running are one and the same - if not, then both SYSDATETIME() and SYSUTCDATETIME() could provide the return value stated in the question.
One must assume that the SQL Server instance is running on "your computer" to narrow it down to SYSUTCDATETIME(). This assumption can't be made based on comparison to "real-life" practice, though, because IRL, people often interact both with SQL Server instances running on their local machines and with SQL Server instances running on servers to which they connect from their local machines, depending on their needs. I did make this assumption, though, because given a scenario where only one answer can be correct, the conditions stated in the problem could lead to two correct answers, and a single assumption that doesn't violate the stated conditions narrows the result to one correct answer, that seems to be the thing to do.
Jason Wolfkill
February 13, 2013 at 10:31 am
(Bob Brown) (2/13/2013)
Thanks. I liked this question. The half hour almost threw me off but I think there could only be one possible answer.
Agreed. This question, though flawed (as most are, when we look closely enough), made us all look at the different ways to return the 'current' date/time in TSQL. I'd say it accomplished its purpose.
Rob Schripsema
Propack, Inc.
Viewing 15 posts - 16 through 30 (of 55 total)
You must be logged in to reply to this topic. Login to reply