Language & Date format
-
I'm having an irritating problem. I'm english, the company I'm working for is english, everyone who works for my company is english. Can I get the default language and date-format to be english (as in the language & date-format used in England, UK)? No, I can't.
I've tried setting the server settings to 'British english', I've even gone so far as to swap the entries of 'us_english' and 'British' in the syslanguages table. But no combination of these works, still whenever I run a new query in Query Analyser, the default language is 'us_english' and the default date-format is 'mdy'.
Please help, none of my developers want to swap to thinking the american way, we're too used to being, well, english.
-
quote:
I'm having an irritating problem. I'm englishYou said it... not me
Ever since the yanks won Waterloo, the Alamo or whatever battle it was when they ousted the Brits, they've thought it a darned good joke to switch the month and day around.
Anyway, regardless of the date format on the server i'm working with, I've always encouraged developers to supply and retrieve/display dates in a yyyy-mm-dd format. It especially eases the pain if you were to move your applications to a differently configured server... or your company gets swallowed by a US conglomerate.
Cheers,
- Mark
Cheers,
- Mark -
The yanks won waterloo???? Jeez, don't tell the french, it's bad enough them thinking we won it ...
It's not just the developers, it's users too, and there are too many of them to explain the foibles of SQL Server to, they want to stick with what they know.
It doesn't bother me at all that SQL Server ships with us_english as default, why would it? What does bother me is that I can't change it on a more than temporary basis!
Whoever said it was right - USA & Britain, two countries divided by the same language.
-
Try checking the default language for the logins. Set to 'English' in EM which equates to 'us_english' language in sysxlogins.
I have found 'British English' equates to 'British' in sysxlogins and expects dates as American (MDY).
p.s. I'm on SQL7 SP4 don't know if this still aplies to SQL2K.
Edited by - davidburrows on 09/12/2003 07:24:47 AM
Far away is close at hand in the images of elsewhere.
Anon. -
Ok, thanks, I tried changing the logins to british and then found that whereas with 'us_english' you could enter 'mm-dd-yy' or 'yyyy-mm-dd' (which i thought went in-line with the datetime & smalldatetime standard formats), with 'british english' it recognises 'dd-mm-yy' (obviously) but if you try putting a 4-figure year first (as you would if you were used to datetime & smalldatetime), it translates as 'yyyy-dd-mm'! how screwy is that???
Does this sound right to anyone?? Sounds like a bug to me ...
-
You found the same as I did. I think it is a quirk of sql. I did the following tests, the first value is what I set a datetime variable to and the second value is the result when displayed.
British English
2003-04-06 2003-06-04
2003-04-22 Error
06-04-2003 2003-04-06
04-06-2003 2003-06-04
22-06-2003 2003-06-22
06-22-2003 Error
22-Jun-2003 2003-06-22
2003-Jun-22 2003-06-22
English
2003-04-06 2003-04-06
2003-04-22 2003-04-22
06-04-2003 2003-06-04
04-06-2003 2003-04-06
22-06-2003 Error
06-22-2003 2003-06-22
22-Jun-2003 2003-06-22
2003-Jun-22 2003-06-22
This is why I set my logins to British and only use yyyy-mm-dd, dd-mmm-yyyy or yyyy-mmm-ddd formats.
Far away is close at hand in the images of elsewhere.
Anon. -
If you enter the date as yyyymmdd with no hyphens or slashes between the various parts of the date, then SQL Server correctly interprets the whether you're English, Scottish, American, Australian etc
-
Hey Faubus, we Aussies have the same problem. Here's how I get around it.
In stored procedures include the statement
SET DATEFORMAT dmy
at the top of the procedure.
Also, always pass dates back and forth as varchar formatted using the 3 character month name. This format will never be misinterpreted by SQL Server. No matter what order you have the date elements in. It could be mmm yyyy dd or yyyy dd mmm or any other combination.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
The most reliable way I found is using convert. set dateformat only works for input and that can really confuse users as they see the date they just entered back with day and month switched!
For input:
insert into tablea convert(datetime, '30/1/2000', 103)
insert into tablea convert(datetime, '30-1-2000', 105)
For output:
select convert(varchar,a1,103) from tablea
There is a complete list of formats in the Book online.
-
Yeah, I think everyone who isn't from the US must have the same problem. Either setting the default language or using SET DATEFORMAT or using CONVERT to convert strings all work, except in the instance where an input parameter is datetime and you're executing the SP from squirrel analyser. There are ways round these, it's just an irritating inconsistency. Sorry, I meant 'design feature'.
-
I haven't run into any problems executing from Query Analyzer, as I mentioned in my previous post I just use 3 character month names and it works wonderfully.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
I haven't run into any problems executing from Query Analyzer, as I mentioned in my previous post I just use 3 character month names and it works wonderfully.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
For input routines, I try to persuade developers to use (in VB)
format(dMyDate, "dd mmm yyyy")which puts a three-letter month in, which is unambiguous. Hurrah!
Of course, hardly any of 'em have taken this on board, muttermutter...
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply