InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 07:17:26 GMT20http://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]Mr Quillz (5/13/2011)[/b][hr]Something is wrong with that answer provided.[/quote]No, there isn't. Try reading the rest of the thread.Fri, 13 May 2011 03:01:56 GMTpaul.knibbshttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxSomething is wrong with that answer provided.Fri, 13 May 2011 02:54:36 GMTMr Quillzhttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxok, then I guess it tricked me. :~)Tue, 19 Apr 2011 16:52:45 GMTdennisphttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]dennisparks (4/19/2011)[/b][hr]I don't remember the question stating to change the default date setting.under default settings, they work fine.[/quote]The question clearly stated which ones work 'regardless of any date format or language settings?'.Tue, 19 Apr 2011 16:38:21 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxI don't remember the question stating to change the default date setting.under default settings, they work fine.Tue, 19 Apr 2011 16:21:37 GMTdennisphttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]dennisparks (4/19/2011)[/b][hr]All 4 work on my SQL 2008.bogus question...[/quote]Really?!?!Try running eitherSET DATEFORMAT ydmor SET LANGUAGE Britishand then see what your results are.Like someone already pointed out BOL states that DATE and DATETIME2 have Default String literals of YYYY-MM-DD the others do not.Tue, 19 Apr 2011 12:53:15 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxAll 4 work on my SQL 2008.bogus question...Tue, 19 Apr 2011 12:21:20 GMTdennisphttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxFortunately I got it right because I somehow knew it'd be a tricky question and I'm studying the new data types in MSSQL 2008. :-DBut I've never tested them that way and I see a lot of errors when dealing with datetime/smalldatetime conversion to other data types around the internet.Hoepfully those new date and time types will make our lives easier.Thank you for the question. :)Best regards,Thu, 14 Apr 2011 11:12:01 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]Duncan Pryde (4/13/2011)[/b][hr][quote][b]Henrico Bekker (4/12/2011)[/b][hr]All 4 options work? one of those questions again...pick 2 out of 4 correct answers...a quessing game again.[/quote]Try this:[code="sql"]SET DATEFORMAT ydmGOSELECT CAST('2011-04-28' AS DATETIME)[/code]or this:[code="sql"]SET LANGUAGE BritishGOSELECT CAST('2011-04-28' AS DATETIME)[/code]and do the same for the other date types, and you'll see that DATETIME and SMALLDATETIME produce an error while DATE and DATETIME2 don't.So no guessing required!Duncan[/quote]Just look up the various data types in books online.Only DATE and DATETIME2 have Default String literals... the others do not. And the default string literals are YYYY-MM-DD :)Thu, 14 Apr 2011 08:59:02 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]rmechaber (4/13/2011)[/b][hr]Duncan, nice follow-up to your last question. I'm gratified? relieved? to see so many wrong answers, as I thought everyone else but me knew what Tibor Karaszi pointed out in his blog: SQL Server DATETIME and SMALLDATETIME types are not ANSI-compliant.As a follow-up to this issue, I was quite surprised to learn that (apparently), unless you have purchased and installed a [u]local[/u] version of SQL Server, you cannot change the language settings at the [u]server[/u] level, only for individual logins or query sessions. (You apparently can issue the command without error, but it makes no difference.) It may be possible to make a registry change to force the language change; see [url=http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891[/url].I cannot find official confirmation of this behavior at Microsoft, but here's a relevant article excerpt from [url=http://www.sqlservercurry.com/2010/11/change-default-language-for-sql-server.html]http://www.sqlservercurry.com/2010/11/change-default-language-for-sql-server.html[/url][quote]If you have not installed the localized version of SQL Server, the default language is US English. If you need to change the default language on this machine, then you will have to change the default language for individual logins, as doing it on a server level won't work.[/quote]Thanks again for great questions,Rich[/quote]You can't change the installation language (whatever that means) but you can change the server default language. I did that myself recently, I think following that discussion in which you first posted the Tibor Karaszi link. Once you change the language, all subsequently created logins will use the new default language, but all existing logins will use the previous default language, or whatever language they were created with.Thu, 14 Apr 2011 01:11:39 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]Cliff Jones (4/13/2011)[/b][hr]A very good question. It is unfortunate that some are not getting the message that it is trying to convey, that not all date formats are portable across all language settings. If you ever have to port your application to a different country or language, this can cause a lot of rework.[/quote]It's slightly disappointing that the percentage of right answers is almost exactly the same as last week, but then it often takes a while for new concepts to sink in. Despite being in the UK, most of the servers we deal with are installed with US_English as the default language. I reckon it was several years before people (including me) noticed that the 'yyyy-MM-dd' format didn't work in "British", and I vaguely remember trying to debug an application that used the format and had stopped working when the database was moved to another server. It took ages to work out what the problem was, but once we had, the mistake was never made again!Thu, 14 Apr 2011 01:06:26 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxHi DuncanAbout a week ago, you asked just about the same question, with the same words (choices was different) and the language was part of the answer... I should have got this one right... but I did not. I did not think. It was too easy! I knew that all fourth would work on my machine! ... did not think about the language at all... I should remember now! Thanks for the good question!Wed, 13 Apr 2011 20:54:05 GMTtilew-948340http://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]Duncan Pryde (4/13/2011)[/b][hr][quote][b]Nakul Vachhrajani (4/13/2011)[/b][hr]Thank-you, Duncan for this wonderful series![/quote]You're welcome - although I'd hesitate to call 2 questions a series! Unless you're expecting more? :blink:[/quote]I think the awesome title choice gives it that "series" feel.Nice question, I just need to pay more attention to the "select 2" part next time.Wed, 13 Apr 2011 14:18:38 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]CirquedeSQLeil (4/13/2011)[/b][hr][quote][b]paul.knibbs (4/13/2011)[/b][hr]Oh, I agree, the question was perfectly clear--... :-D[/quote]I agree - I felt the question was very precise and clear in the meaning and intent.[/quote]Me too. It's unfortunate that QotD is unlikely to teach reading comprehension, which has clearly been lacking rather a lot judging by the number of responses that claim all four work.Wed, 13 Apr 2011 10:59:45 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxDuncan, nice follow-up to your last question. I'm gratified? relieved? to see so many wrong answers, as I thought everyone else but me knew what Tibor Karaszi pointed out in his blog: SQL Server DATETIME and SMALLDATETIME types are not ANSI-compliant.As a follow-up to this issue, I was quite surprised to learn that (apparently), unless you have purchased and installed a [u]local[/u] version of SQL Server, you cannot change the language settings at the [u]server[/u] level, only for individual logins or query sessions. (You apparently can issue the command without error, but it makes no difference.) It may be possible to make a registry change to force the language change; see [url=http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891[/url].I cannot find official confirmation of this behavior at Microsoft, but here's a relevant article excerpt from [url=http://www.sqlservercurry.com/2010/11/change-default-language-for-sql-server.html]http://www.sqlservercurry.com/2010/11/change-default-language-for-sql-server.html[/url][quote]If you have not installed the localized version of SQL Server, the default language is US English. If you need to change the default language on this machine, then you will have to change the default language for individual logins, as doing it on a server level won't work.[/quote]Thanks again for great questions,RichWed, 13 Apr 2011 10:08:33 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]CirquedeSQLeil (4/13/2011)[/b][hr][quote][b]paul.knibbs (4/13/2011)[/b][hr]Oh, I agree, the question was perfectly clear--... :-D[/quote]I agree - I felt the question was very precise and clear in the meaning and intent.[/quote]I can only assume that a lot of people have no idea that date format or language settings exist or what they might do.Wed, 13 Apr 2011 10:03:03 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxA very good question. It is unfortunate that some are not getting the message that it is trying to convey, that not all date formats are portable across all language settings. If you ever have to port your application to a different country or language, this can cause a lot of rework.Wed, 13 Apr 2011 10:00:17 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxExcellent question. Keep them coming Duncan.Wed, 13 Apr 2011 09:55:56 GMTSuriihttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxGood question, but tough to get it right :-)Wed, 13 Apr 2011 09:53:50 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]paul.knibbs (4/13/2011)[/b][hr]Oh, I agree, the question was perfectly clear--... :-D[/quote]I agree - I felt the question was very precise and clear in the meaning and intent.Wed, 13 Apr 2011 09:46:40 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxAnother great question, thanks!Wed, 13 Apr 2011 09:41:15 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]nelsonj-902869 (4/13/2011)[/b][hr]Even tho the question stated..."regardless of any date format..." this is another question with a questionable question, since I found that all 4 answers do produce an answer without any errors. It's almost getting to the point where the QotD is questionable(?)[/quote]It's not questionable at all. The question was asking which of the four things work no matter what your date format is--try changing your date format to something like dmy and you should find two of them no longer work, so obviously they're not the right answers!Wed, 13 Apr 2011 09:14:57 GMTpaul.knibbshttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxEven tho the question stated..."regardless of any date format..." this is another question with a questionable question, since I found that all 4 answers do produce an answer without any errors. It's almost getting to the point where the QotD is questionable(?)Wed, 13 Apr 2011 09:12:20 GMTnelsonj-902869http://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]al_kessler (4/13/2011)[/b][hr]Not sure what I'm missing but the only one that failed for me was the datetime. Tried British, German, Italian, US_English. Tried dateformat as ymd, dmy, mdy and the only one that failed was datetime when set to dmy. All this on SQL 2008 SP2.[/quote]Smalldatetime should fail under exactly the same circumstances as datetime since as far as I can tell their behaviour is identical. It certainly fails for me, anyway:[code="sql"]SET DATEFORMAT ydmGOSELECT CAST('2011-04-28' AS SMALLDATETIME)[/code]DuncanWed, 13 Apr 2011 09:05:15 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxNot sure what I'm missing but the only one that failed for me was the datetime. Tried British, German, Italian, US_English. Tried dateformat as ymd, dmy, mdy and the only one that failed was datetime when set to dmy. All this on SQL 2008 SP2.Wed, 13 Apr 2011 08:55:41 GMTal_kesslerhttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxNice question. I'm learning more about date types than I expected!Wed, 13 Apr 2011 08:43:57 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]bitbucket-25253 (4/13/2011)[/b][hr][quote][b]Duncan Pryde (4/13/2011)[/b][hr][quote][b]bitbucket-25253 (4/13/2011)[/b][hr]A good question. As of 9:20 AM only 28 percent correct answers. Your QOD has achieved one of the major objectives ... teaching [/quote]Thanks - although I'd have hoped the percentage of correct answers would have been considerably higher given the answers were in the links I'd posted as part of the explanation of last week's question. But then it looks like some people are getting it wrong because they misread the question, not because they didn't know the answer.Maybe I will need to revisit this topic in another QotD :blink:[/quote]Indeed, definitely post another QOD concerning dates and time. if memory serves me correctly, last weeks percent correct was about 28 so it is an area that needs more teaching.[/quote]OK, you've twisted my arm. I'll get onto it after my current workload has subsided a bit.On the subject of things it would be good to have QotD about, I've started another topic here if anyone would like to contribute:[url]http://www.sqlservercentral.com/Forums/Topic1092873-4-1.aspx[/url]Wed, 13 Apr 2011 08:35:31 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxOh, I agree, the question was perfectly clear--my problem was that I worked out which two wouldn't work, and then went ahead and ticked them without thinking about what I was doing rather than ticking the OTHER two! :-DWed, 13 Apr 2011 08:12:34 GMTpaul.knibbshttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxNice question. It seemed clear to me also that you needed to account for different language or format settings.Wed, 13 Apr 2011 08:10:45 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]Duncan Pryde (4/13/2011)[/b][hr][quote][b]bitbucket-25253 (4/13/2011)[/b][hr]A good question. As of 9:20 AM only 28 percent correct answers. Your QOD has achieved one of the major objectives ... teaching [/quote]Thanks - although I'd have hoped the percentage of correct answers would have been considerably higher given the answers were in the links I'd posted as part of the explanation of last week's question. But then it looks like some people are getting it wrong because they misread the question, not because they didn't know the answer.Maybe I will need to revisit this topic in another QotD :blink:[/quote]Indeed, definitely post another QOD concerning dates and time. if memory serves me correctly, last weeks percent correct was about 28 so it is an area that needs more teaching.Wed, 13 Apr 2011 08:08:44 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]Duncan Pryde (4/13/2011)[/b][hr][quote][b]bitbucket-25253 (4/13/2011)[/b][hr]A good question. As of 9:20 AM only 28 percent correct answers. Your QOD has achieved one of the major objectives ... teaching [/quote]Thanks - although I'd have hoped the percentage of correct answers would have been considerably higher given the answers were in the links I'd posted as part of the explanation of last week's question. But then it looks like some people are getting it wrong because they misread the question, not because they didn't know the answer.Maybe I will need to revisit this topic in another QotD :blink:[/quote]Given how many people seem completely oblivious to the impact that collation and language settings have upon how date functions work, I think it would be very useful to the community. Looks like lots of people assume that because it works on US settings it would work anywhere in the world.Wed, 13 Apr 2011 07:58:21 GMTjeff.masonhttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]Carlo Romagnano (4/13/2011)[/b][hr]Thank you very much for this beautiful qotd.It's unbelievable, but true, only new types DATE and DATETIME2 work in any case.[/quote]Which is exactly why I got this right -- I knew that SMALLDATETIME has limits and I thought I remembered that DATETIME had problems, so it made sense that only the two new datatypes would do British without error.Wed, 13 Apr 2011 07:52:00 GMTjeff.masonhttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]bitbucket-25253 (4/13/2011)[/b][hr]A good question. As of 9:20 AM only 28 percent correct answers. Your QOD has achieved one of the major objectives ... teaching [/quote]Thanks - although I'd have hoped the percentage of correct answers would have been considerably higher given the answers were in the links I'd posted as part of the explanation of last week's question. But then it looks like some people are getting it wrong because they misread the question, not because they didn't know the answer.Maybe I will need to revisit this topic in another QotD :blink:Wed, 13 Apr 2011 07:39:05 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxGreat question. Not sure how some people can argue the clarity. Waterford crystal isn't any clearer than this question. Keep 'em coming Duncan.Wed, 13 Apr 2011 07:37:00 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxA good question. As of 9:20 AM only 28 percent correct answers. Your QOD has achieved one of the major objectives ... teaching Wed, 13 Apr 2011 07:22:08 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]Koen Verbeeck (4/13/2011)[/b][hr]Dammit, I got it wrong because I've misread the question.Anyway, it was a great question, thanks Duncan.[/quote]Know the feeling - note to self - read the question at least twice !Wed, 13 Apr 2011 07:14:39 GMTStuart Davieshttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxI definitely learned something this morning, thanks.Wed, 13 Apr 2011 06:48:25 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxD'oh! After I'd submitted my answer I realised I'd ticked the two that triggered the error, NOT the two that would complete successfully...my reading comprehension clearly needs work. :crying:Wed, 13 Apr 2011 06:32:43 GMTpaul.knibbshttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspx[quote][b]Henrico Bekker (4/13/2011)[/b][hr]question doesnt state that you need to SET LANGUAGE....[/quote]Look closer... (bolded emphasis mine)[quote]Which of the following statements will complete successfully - i.e. not cause an error - in SQL Server 2008, [b]regardless of any date format or language settings[/b]? [/quote]This is a good question... thanks!Wed, 13 Apr 2011 05:48:04 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1092547-2901-1.aspxGreat question perfectly worded. It was quite clear that dateformat was relevant to the question and that you were looking for only the 2 statements that would work under [u]all[/u] conditions. I hate the pedantics on here.However, without 2008 to play on, and after much unsuccessful digging around with google, I ended up guessing wrong. Bummer!Learned something new though, which is always the primary objective :-)Keep them coming Duncan.Wed, 13 Apr 2011 03:09:19 GMTDugyC