June 6, 2012 at 9:12 am
great question Lynn - something I had not encountered so great lesson for me here! cheers
June 6, 2012 at 9:27 am
Now I know why this question is worth 2 points:
One point is for the question per se.
The other point is due for the high grade of difficulty on read the answer options 😎
Good excercise for the eyes too, but now I need a glasses with more gain 😛
June 6, 2012 at 9:54 am
1 minute of thinking, 10 minutes of looking for the right answer among those offered... But a nice question, anyway - thanks, Lynn!
June 6, 2012 at 11:52 am
I would like to hear a SQL philosopher/historian explain how this situation came about. It seems such a violation of language orthogonality - lying in wait to create bad data.
June 6, 2012 at 12:44 pm
Very interesting behavior. Thanks for the question.
June 6, 2012 at 3:32 pm
June 7, 2012 at 1:41 am
When you run the follow script in SSMS, which of the outputs do you expect to be returned?
Surely all answers are right, unless chooses an unexpected answer? 😛
I was aware of the point the question was looking to make, but couldn't be bothered fighting through the text to prove it so picked one at random. I got it 'wrong'.
June 7, 2012 at 3:22 am
The posert should have limited to only two options.
even though we knew the answer very difficult to spot on from the options.
Seems like a test for the eyes rather than SQLserver. 😀
June 7, 2012 at 5:32 am
I get it, I should have used the DATE data type instead of DATETIME2 for the question so I could have left off the time portion on the answers. How about everyone just quit complaining about that, this wasn't a test for the eyes.
I found out about a behaviour regarding datetime conversions that existed starting in SQL Server 2008 with the addition of several new date/time data types that I wasn't aware of and that wasn't even described why it was happening and I dug into it more. I thought it would be a great QotD and submitted it.
Also, for the code as presented, there is only one correct answer.
The reason I didn't give only 2 possibilities is that I didn't want it to be a 50/50 guess. I was trying to make it a bit more challenging than that.
June 7, 2012 at 9:32 am
Koen Verbeeck (6/5/2012)
Very interesting question, but those answers were hard to read 🙂
+10
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
June 8, 2012 at 8:20 am
AndyK-565224 (6/6/2012)
I would like to hear a SQL philosopher/historian explain how this situation came about. It seems such a violation of language orthogonality - lying in wait to create bad data.
I agree - it seems like a bad idea.
Found this chart on BOL "Using Date and Time Data"
There are some subtle format differences that seem very arbitrary to me.
June 8, 2012 at 9:44 am
Carla Wilson-484785 (6/8/2012)
There are some subtle format differences that seem very arbitrary to me.
It's often a backward-compatibility thing.
June 8, 2012 at 2:57 pm
Why wouldn't all selects error out as in the example in MS189491 when the set dateformat's used both end with year but all the dates start with the year?
June 13, 2012 at 8:50 am
Good way to return from vacation. Good question, but reading the answers was a new experience in eyeball manipulation:crazy:
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
June 13, 2012 at 10:25 am
Very good question.
I don't know why people are fussing so much about the answer format; it did put me of at first, made me leave the question for when I might have time to wade through it, but when I came back to the question and worked out what the output should be (nice and easy, not a lot of thionking time) I saw that there would only be one occurrence of 04-11 instead of 11-04 and that would be in the first column of the second select's output. Even with all that rather small-print output that was easy to look for - just took a few seconds - so the answer format didn't make it hard at all.
Something people should beware of (that isn't obvious from this question) is that when a yyyy-aa-bb date string is converted to datetime using cast or using convert with null style parameter dateformat determines whether it is treated as ODBC canonical date format (dateformat dmy) or as ANSI SQL date format (dateformat mdy); while if a style parameter is included with convert then either an error is generated regardless of dateformat or it is converted as if it were ODBC canonical regardless of dateformat. But if cast or conversion with null style parameter is done to datetime2 this format is always interpreted as ODBC canonical, and there is no way or getting from a dateime2 value to a string that uses ANSI SQL format using a single case or conversion - ANSI SQL is not one of convert's supported styles, although in with dateformat mdy and datatype datetime or smalldatetime omitting the style gives you that format.
Tom
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply