SQL CHOOSE
-
Easy one for the day! Thanks Bill:-)
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
I have to disagree with most of the comments posted so far. I do NOT think that this is a good question at all.
First, the date format. I totally disagree with the people who post that you should simply know to add SET DATEFORMAT or SET LANGUAGE if you get an error on the insert. One of the answer options was the exact error message you get on a failed conversion. Without that answer option, I would be inclined to agree that you should be able to see through the error, but not as it is.
The format yyyy-mm-dd is NOT a safe format. It does work in American English, but not in British English - just put "SET LANGUAGE British;" in front of the batch (or run it as is on a server that was installed with default options on a British OS) and rerun to see that this returns the exact error message that is included as an answer option. Or try other European languages, such as French, Dutch, or German, to see some foreign language counterparts of that message. Now graned, this does not make any sense at all (I know of no European country that ever uses yyyy-dd-mm as date format), but this is how MS originally implemented it, and it's what we're stuck with "for compatibility reasons". MS did fix it for the newer datatypes; when converting to e.g. datetime2 or date, the code as given will run in each locale. But for the original datetime data type that is used in the question, the only date formats that are guaranteed to be unambiguous are "yyyymmdd" (date only - no dashes!!) or "yyyy-mm-ddThh:mm:ss.mmm" (date and time - with dashes, with an uppercase T to separate date from time, and with colons; the dot and milliseconds are optional).
Second, the other incorrect answer. From the explanation, I see that the author intended this to catch people who thought that the CHOOSE function would be based on the PositionId column - but if that were the case, it would return NULL, January, February, March, April, and that was not the option.
A much more interesting wrong answer would have been February, September, August, March, July - the result one would get if CHOOSE were implemented to be zero-based rather than one-based.
To the submitter of the question: Please don't be discouraged by my harsh tone. That's just how I tick (ask any book author who has had the bad luck to have me as their technical editor, they'll tell you I can get far worse!) I do appreciate that you put in the effort to submit a question. But I also want to make sure you know what errors I think you made in this question, so that you can avoid them for your next QotD submission. Never forget that making new errors is far more fun than repeating your old errors!
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hugo Kornelis (6/19/2013)
To the submitter of the question: Please don't be discouraged by my harsh tone. That's just how I tick (ask any book author who has had the bad luck to have me as their technical editor, they'll tell you I can get far worse!) I do appreciate that you put in the effort to submit a question. But I also want to make sure you know what errors I think you made in this question, so that you can avoid them for your next QotD submission. Never forget that making new errors is far more fun than repeating your old errors!I agree 100% on this. I have been on the receiving side for Qotds many times, especially by Hugo. Believe me, this has helped me a lot. You will rarely find someone who can correct you and guide you. Not only in Qotds' I have gone wrong many times in other forum posts and happy that people like Hugo and Gail have corrected me. This really helps.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
As with most of the QOTD submissions - even if the question and/or answer options are flawed, there is value for me in researching the subject. Some days I get frustrated, but I always appreciate the attempt to teach me something.
-
It should be noted that this requires SQL2012. In SQL2008R2 & prior, this error message will be returned as CHOOSE is not yet supported.
Msg 195, Level 15, State 10, Line 19
'CHOOSE' is not a recognized built-in function name.
This logic would require a CASE statement (or nested IF's) for pre-2012.
-
Carlo Romagnano (6/19/2013)
If the statement begins withset dateformat ymd,the qotd would be perfect.
and an order by in the select statement. 😉
-
Hugo Kornelis (6/19/2013)
The format yyyy-mm-dd is NOT a safe format. It does work in American English, but not in British English - just put "SET LANGUAGE British;" in front of the batch (or run it as is on a server that was installed with default options on a British OS) and rerun to see that this returns the exact error message that is included as an answer option.
That's odd. I have a UK machine, set to UK regional settings, and it has a SQL 2012 Express installation that I'm pretty sure was installed with all the defaults--yet it appears to have defaulted to English rather than British English.
-
I have a feeling that this is one of those new functions that I will never actually use. Thanks for the 2012 question though!
-
batgirl (6/19/2013)
As with most of the QOTD submissions - even if the question and/or answer options are flawed, there is value for me in researching the subject. Some days I get frustrated, but I always appreciate the attempt to teach me something.I usually learn a lot from Hugo's analyses.
-
Thanks for the question, Bill.
----------------------------------------------------------------------------
Yeah, well...The Dude abides.
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
---------------------------------------------------------------------------- -
Hugo Kornelis (6/19/2013)
or run it as is on a server that was installed with default options on a British OSWe can't do that, as there is no British OS which supports SQL Server; for Ingres you can find a British OS that supports it (if ICL's VME still counts as British now that the Japanese own the company) and there used to be a British OS that supported Oracle, but there has never been one that supports SQL Server.
Tom
-
Thanks for all the feedback. I definitely didn't consider the different date formats and how it would change depending on the language.
Besides the date formats, is there any other data types that need to be considered for formatting based on language? Also, how do you decide the best tag for your question?
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455 -
None of the answers is correct. Choose is a 2012 function, so it doesn't work in 2008 R2, which is the *assumed* SQL version here. Nowhere in the question do I see an overriding statement to state the exception.
-
james.of.rivendell (6/19/2013)
None of the answers is correct. Choose is a 2012 function, so it doesn't work in 2008 R2, which is the *assumed* SQL version here. Nowhere in the question do I see an overriding statement to state the exception.Noted, I will make sure that is shown in the next question I create. We can assume, though, that since none of the answers were "doesn't work in this version of SQL", that we would be using a version of SQL that does support that function.
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455 -
Thank you....
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply