March 28, 2011 at 7:11 am
I had always used the ANSI syntax thinking it was multilanguage, only to discover later that it wasn't. The reason I'd never noticed is probably that almost all our servers are set to US_English, even though we're in the UK. I knew the 'dd-MM-yyyy' format was not multilanguage so had never used it.
Interestingly, I've changed my SQL 2000 box to default to British English using the properties box in Enterprise Manager, but when I open a new connection to the server in QA or SSMS and run "SELECT @@LANGUAGE" it always returns "us_english". I can't find an option in QA or SSMS that would override the server setting, so I'm a bit mystified.
Check the default language setting for your login. These are independent of the server setting.
If you change the default language, restart the service, and create a new login, what language does it default to?
But I just found a discussion (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891) that suggests changing the server language may not stick? Near the bottom is a registry hack to override the default language setting.
Rich
March 28, 2011 at 7:22 am
I had always used the ANSI syntax thinking it was multilanguage, only to discover later that it wasn't. The reason I'd never noticed is probably that almost all our servers are set to US_English, even though we're in the UK. I knew the 'dd-MM-yyyy' format was not multilanguage so had never used it.
Interestingly, I've changed my SQL 2000 box to default to British English using the properties box in Enterprise Manager, but when I open a new connection to the server in QA or SSMS and run "SELECT @@LANGUAGE" it always returns "us_english". I can't find an option in QA or SSMS that would override the server setting, so I'm a bit mystified.
Curiouser and curiouser:
From http://www.sqlservercurry.com/2010/11/change-default-language-for-sql-server.html comes this warning that to change the server language setting, your software must be a localized version of SQL server:
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.
I don't have a localized version to test this on, but, if true, this would go a long way in explaining why our UK colleagues see no problems entering dates as '2011-02-28' even though they thought their servers were set to British English.
Rich
March 28, 2011 at 7:29 am
rmechaber (3/28/2011)
I had always used the ANSI syntax thinking it was multilanguage, only to discover later that it wasn't. The reason I'd never noticed is probably that almost all our servers are set to US_English, even though we're in the UK. I knew the 'dd-MM-yyyy' format was not multilanguage so had never used it.
Interestingly, I've changed my SQL 2000 box to default to British English using the properties box in Enterprise Manager, but when I open a new connection to the server in QA or SSMS and run "SELECT @@LANGUAGE" it always returns "us_english". I can't find an option in QA or SSMS that would override the server setting, so I'm a bit mystified.
Check the default language setting for your login. These are independent of the server setting.
That's got it. Works as expected now.
March 28, 2011 at 7:38 am
rmechaber (3/28/2011)
I had always used the ANSI syntax thinking it was multilanguage, only to discover later that it wasn't. The reason I'd never noticed is probably that almost all our servers are set to US_English, even though we're in the UK. I knew the 'dd-MM-yyyy' format was not multilanguage so had never used it.
Interestingly, I've changed my SQL 2000 box to default to British English using the properties box in Enterprise Manager, but when I open a new connection to the server in QA or SSMS and run "SELECT @@LANGUAGE" it always returns "us_english". I can't find an option in QA or SSMS that would override the server setting, so I'm a bit mystified.
Curiouser and curiouser:
From http://www.sqlservercurry.com/2010/11/change-default-language-for-sql-server.html comes this warning that to change the server language setting, your software must be a localized version of SQL server:
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.
I don't have a localized version to test this on, but, if true, this would go a long way in explaining why our UK colleagues see no problems entering dates as '2011-02-28' even though they thought their servers were set to British English.
Rich
As far as I know, setting a server to British doesn't affect things like error messages, since they're in English anyway. I also don't recall there being any British option during installation, though I could be wrong.
March 29, 2011 at 2:51 am
In MS SQL SERVER 2005 the create stament will work if you remove the extra comma but in MS SQL SERVER 2008 it won't work as Date is a reserved word in SQl 2008.
March 29, 2011 at 7:31 am
sandippani (3/29/2011)
In MS SQL SERVER 2005 the create stament will work if you remove the extra comma but in MS SQL SERVER 2008 it won't work as Date is a reserved word in SQl 2008.
If you try it, you'll see that the create will work even if you don't remove the extra comma, and that in SQL2008 it will work, even though date is a reserved word.
create table #test
(date datetime,
datetime date,
int int,
varchar text,
text float,
)
:w00t:
March 29, 2011 at 8:00 am
rmechaber (3/28/2011)
Tom Thomson: it would be interesting if you still have access to those databases to poke around and see what kind of results you get. You may find that there are per-user or per-query language settings that are different from the server settings. In any case, this confirms that the DATE data type is not language-independent for the ANSI/ISO syntax of 'yyyy-mm-dd'.Rich
Unfortunately I no longer have access to systems belonging to Neos Interactive's customers (I left the company 21 months ago), and the servers owned by Neos Interactive in London have been taken apart and either scrapped or completely rebuilt now that the UK company is only handling media licensing issues with all development and support being in the middle east. I would very much like to get to the bottom of it: the change we made caused dd/mm/yy to stop failing and mm/dd/yy to start failing (for dd bigger than 12; for dd less than 13 the changes were between working incorrectly and working correctly, and vice versa) but had no impact at all on yyyy-mm-dd (worked before and after the change). It's possible that we had done something strange to the server to make it always handle yyyy-mm-dd correctly, but I can't imagine what, and if it had been being done for all new server installs I would have known about it (it would have required my authorisation for installation on a customer's system).
Tom
March 29, 2011 at 8:25 am
Toreador (3/29/2011)
sandippani (3/29/2011)
In MS SQL SERVER 2005 the create stament will work if you remove the extra comma but in MS SQL SERVER 2008 it won't work as Date is a reserved word in SQl 2008.If you try it, you'll see that the create will work even if you don't remove the extra comma, and that in SQL2008 it will work, even though date is a reserved word.
create table #test(date datetime,
datetime date,
int int,
varchar text,
text float,
)
:w00t:
This should have been posted as a new question. Most of the developers having more than 10 years of experience in SQL Server may fail to answer right 🙂
May 1, 2011 at 2:49 am
extra comma in select statement fails to execute, nothing happens when extra comma in CREATE TABLE ..... not really consistent !
so I failed to respond correctly
May 25, 2011 at 7:09 am
i thought the same that because there is extra column in the create statement.
August 19, 2011 at 9:59 am
Actually you get when using british_english
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
grrr!
March 2, 2012 at 2:19 pm
I expected an error would be thrown due to the extra comma in the table creation, hence causing the SQL statement to fail.
Thanks.
Viewing 12 posts - 91 through 101 (of 101 total)
You must be logged in to reply to this topic. Login to reply