March 18, 2011 at 9:17 pm
Interesting. I have to admit that I had to try it out to see how that extra comma would work
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 19, 2011 at 4:37 am
OCTom (3/18/2011)
Hugo,I really resent your use of stupid. Whom does the rest of the world turn to when they need help? Whom does the world turn to for leadership? Mostly it's the U.S. So, get off your high horse. And, to include the Japanese as stupid during their time of crisis is horrible.
I await your apology, though, I don't know that I'd accept it.
I think all that's a pretty disgraceful comment - we can do without this sort of nationalistic nonsense, whether from US Americans or from anyone else. I won't await YOUR apology, as I imagine that your arrogance will mask from you the fact that you owe one to every non-US citizen in this SQLServerCentral community.
The question of date formats has come up a pretty large number of times in QoTD discussions in the last two or three years, and it amazes me that it continues to come up. Most people in this world don't use the US/Japanese date formats, but US people continue to assume that every computer system in the world is automatically installed with the USA as its default locale and US English as its default language. That is arrogant nonsense of the first order. I was very surprised to see Ron fall into that trap, as he certainly knows better - I guess it was just a momentary aberration.
Tom
March 19, 2011 at 4:53 am
bitbucket-25253 (3/18/2011)
I don't mind if you ignore me, but are you at least planning to address the concerns of those other 9 people?
I relied on http://support.microsoft.com/kb/173907
which states:
By default, the date format for SQL server is in U.S. date format MM/DD/YY, unless a localized version of SQL Server has been installed
The item goes on to say how that default value can be altered. I thus assumed (and yes I know assumed means make an a$$ out of you and me). The question does NOT state that a localized version was utilized. If a localized version was installed I again assumed that the user would be aware of this and adjust his/her answer to one using the default date format.
Ron, the URL does include a disclaimer:
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
. The last update of the article was late 2003, so presumably it describes versions of SQL Server that were already out of support then - nothing that anyone is likely to use today.
The default dateformat for a login is derived from the default language for the login, but the actual dateformat to be used can now (SQL 2005 and later) be set without changing language using SET DATEFORMAT (and all of the 6 possibilities mdy, dmy, ymd, ydm, myd, and dym are now available) - that's a big change from what the URL describes.
In some countries when you buy SQL Server it comes with the local language as the general default language (that is, the default language for all logins except any for which a different default language is specified individually).
I used to do default installs on Windows 2003 Server using SQLServer downloaded from the MS website and they turned out to get UK English as default language if the Windows 2003 Server default was UK English, and US English if the WIN 2003 Server default was US English, so the default on installation of SQL appeared to depend on the platform OS default. I've seen suggestions that this no longer (SQL 2008 R2) happens and it's now a pain to change default language - I don't know what the truth of that is (and can't find any documentation) but if it's true I think it's a sill step backwards by MS.
Tom
March 19, 2011 at 12:22 pm
Tom.Thomson (3/19/2011)
The question of date formats has come up a pretty large number of times in QoTD discussions in the last two or three years, and it amazes me that it continues to come up. Most people in this world don't use the US/Japanese date formats, but US people continue to assume that every computer system in the world is automatically installed with the USA as its default locale and US English as its default language. That is arrogant nonsense of the first order. I was very surprised to see Ron fall into that trap, as he certainly knows better - I guess it was just a momentary aberration.
As a Brit, I'm familiar with the issues surrounding implicit conversions of strings to dates simply because they inevitably crop up due to the fact that some servers get installed with the default settings - i.e. US. I have therefore learnt the hard way to use the 'yyyyMMdd' format, or do an explicit conversion. If I'd learnt my trade in the US, only using US-locale servers I may not be so concious of it.
Given that it causes so much heartache, does anyone know why SQL Server allows implicit string-to-date conversion for anything other than ANSI or ISO format dates? Surely it causes more problems than it solves?
March 19, 2011 at 2:00 pm
Tom.Thomson
I was very surprised to see Ron fall into that trap, as he certainly knows better - I guess it was just a momentary aberration.
It was, but after being "spanked" so often, I doubt if I will do it again, or at least I hope not to do so again.
I am attempting to look at this on the bright side. Hopefully a large number of Americans who read these comments, will learn as well, and gosh that is the idea of the QOD, is it not?
And Tom thanks for being so gracious, it is appreciated.
March 21, 2011 at 3:02 am
Duncan Pryde (3/19/2011)
Given that it causes so much heartache, does anyone know why SQL Server allows implicit string-to-date conversion for anything other than ANSI or ISO format dates? Surely it causes more problems than it solves?
Backward compatibility?
March 22, 2011 at 2:35 am
no. it will not display. Bcoz insertion is not happening then how can display sum?
then creating table is happening and insertion is not happening and sum is coming null in sql server2005
March 22, 2011 at 3:05 am
Toreador (3/21/2011)
Duncan Pryde (3/19/2011)
Given that it causes so much heartache, does anyone know why SQL Server allows implicit string-to-date conversion for anything other than ANSI or ISO format dates? Surely it causes more problems than it solves?Backward compatibility?
Sounds very plausible
March 22, 2011 at 3:16 am
interesting question but can anyone let me know why the create statement wouldn't of failed? interested to see why thats all
March 22, 2011 at 8:21 am
Tom.Thomson (3/19/2011)
The question of date formats has come up a pretty large number of times in QoTD discussions in the last two or three years, and it amazes me that it continues to come up.
Given that we've had these discussions so often (and not just about dates - I recall a very heated exchange about table design and the 'right' data type for a name column - VARCHAR or NVARCHAR - that got quite animated along broadly US/everyone else lines) perhaps someone with more time, public spirit, knowledge and writing ability than I would like to produce an article looking at the issue and associated pitfalls?
OK, it's a bit cheeky of me as I'm not going to do such a thing (you know... time, public spirit, knowledge, ability issues!)... but it could help to inform a broader audience than this?
Cheers,
Dave.
March 22, 2011 at 9:10 am
A fascinating and scary exploration of how SQL Server doesn't always follow ANSI syntax for dates:
http://www.karaszi.com/SQLServer/info_datetime.asp.
A salient quote:
Warnings and common misconceptions
Let me say this again: you don't want to use a combination of format and type which isn't language neutral unless you make sure that you have the correct DATEFORMAT and LANGUAGE settings in your applications.
The Numeric format can use dash (-), dot (.) or slash (/) as separator. The rules for how SQL Server parses the string doesn't change depending on the separator. A common misconception is that the ANSI SQL format (sometime a bit incorrectly referred to as the "ISO format"), 1998-02-23, is language neutral for smalldatetime and datetime. It isn't. It is a numeric format and hence it is dependent on the SET DATEFORMAT and SET LANGUAGE setting:
SET LANGUAGE us_english
SELECT CAST('2003-02-28' AS datetime)
-----------------------
2003-02-28 00:00:00.000
SET LANGUAGE british
SELECT CAST('2003-02-28' AS datetime)
Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Please read the error message closely. It says exactly what the problem is. You specify the datetime value as a string, and because the string isn't formed according to the format you are using and the DATEFORMAT setting, SQL Server cannot convert the string to a datetime value.
I was surprised by this behavior ("behaviour?"), b/c I thought yyyy-mm-dd was universally acceptable as a date to most modern RDMS. It isn't. Not always.
So even avoiding the more obviously ambiguous format of '02/28/2008' vs. '28/02/2008', it can be less than straightforward storing dates in SQL Server.
Rich (U.S.-based DBA)
March 22, 2011 at 11:41 am
rmechaber (3/22/2011)
A fascinating and scary exploration of how SQL Server doesn't always follow ANSI syntax for dates:
Thanks for that - excellent article.
When trying some of the examples in there, I discovered to my horror (as a UK citizen living and working in the UK) that all our SQL Server instances are set as us_english!
I never knew - I always use YYYY-MM-DD format and (as mentioned in the article) assumed that was language independant - but no, it only works because I'm working in American! :blink:
Also makes me laugh that I got the original QOTD wrong because of the US bias and gently railed against the setter because his code would (obviously) fail here in the UK... and it runs perfectly on all my instances!!
Ho hum... :crazy:
March 22, 2011 at 1:44 pm
dave.farmer (3/22/2011)
rmechaber (3/22/2011)
A fascinating and scary exploration of how SQL Server doesn't always follow ANSI syntax for dates:Thanks for that - excellent article.
When trying some of the examples in there, I discovered to my horror (as a UK citizen living and working in the UK) that all our SQL Server instances are set as us_english!
I never knew - I always use YYYY-MM-DD format and (as mentioned in the article) assumed that was language independant - but no, it only works because I'm working in American! :blink:
Also makes me laugh that I got the original QOTD wrong because of the US bias and gently railed against the setter because his code would (obviously) fail here in the UK... and it runs perfectly on all my instances!!
Ho hum... :crazy:
Dave, that's a candid response, thanks. I thought I was perhaps taking the thread off-topic. If I helped one person, that's good enough for me!
Next thing you know, you'll be swapping your place and chips for Big Macs! 😀
Rich
March 22, 2011 at 2:03 pm
rmechaber (3/22/2011)
dave.farmer (3/22/2011)
rmechaber (3/22/2011)
A fascinating and scary exploration of how SQL Server doesn't always follow ANSI syntax for dates:Thanks for that - excellent article.
When trying some of the examples in there, I discovered to my horror (as a UK citizen living and working in the UK) that all our SQL Server instances are set as us_english!
I never knew - I always use YYYY-MM-DD format and (as mentioned in the article) assumed that was language independant - but no, it only works because I'm working in American! :blink:
Also makes me laugh that I got the original QOTD wrong because of the US bias and gently railed against the setter because his code would (obviously) fail here in the UK... and it runs perfectly on all my instances!!
Ho hum... :crazy:
Dave, that's a candid response, thanks. I thought I was perhaps taking the thread off-topic. If I helped one person, that's good enough for me!
Next thing you know, you'll be swapping your place and chips for Big Macs! 😀
Rich
Make that 2. An excellent link - thanks.
I've already pretty much left the Cod'n'chips and gone over to the culinary dark side - although I quite like the Big Tasty rather than the Big Mac 😉
March 22, 2011 at 7:10 pm
I wondered how SQL Server accepted an extra comma at the end of Create Table statement!
Viewing 15 posts - 61 through 75 (of 101 total)
You must be logged in to reply to this topic. Login to reply