locked
Change language from US English to British english RRS feed

  • Question

  • I have detected a difference between development and production instances of SQL Server. The difference is that the string '2018-02-01' in development that is in US English, 2 is the month and 1 is the day. In Production that is British english 2 is the day and 1 is the month. I want to change the language in development so that production and development works in the same way.

    In the operating system I have seen all the same features regarding to dates, formats, location, ...

    At the instance level both of them have US English.

    At the database level is the difference. So I have changed the default language in Develpment to British english and I have rebooted the service of SQL Server, as long as I'm using SQL Server 2012. And afterwards when I open again a screen to query a database with the default language changed it still has British english. If I use the command

    SELECT @@language, @@langid

    it says us_english, 0

    if I run

    EXEC sp_configure "default language", 23
    RECONFIGURE WITH OVERRIDE

    it says Configuration option 'default language' changed from 23 to 23. Run the RECONFIGURE statement to install.

    In the database I can see that the default language is set to British english. I can reconfigure and restat the service of SQL Server, that

    SELECT @@language

    always retrieve us_english and dates behave as us_english.

    How can I get it to work as British English?

    Friday, August 31, 2018 10:12 AM

Answers

  • Hello,

    The Server Default language is only used when new Logins are created, the effective use language is defined per Login / or per session;

    SELECT Name, default_language_name
    FROM sys.server_principals


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Joan Ferrer Friday, August 31, 2018 11:25 AM
    Friday, August 31, 2018 10:22 AM
  • To add on to Olaf's answer, you can change default language for existing logins with ALTER LOGIN: 

    ALTER LOGIN YourLogin
    WITH DEFAULT_LANGUAGE = british;
    


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Joan Ferrer Friday, August 31, 2018 11:24 AM
    Friday, August 31, 2018 10:37 AM

All replies

  • Hello,

    The Server Default language is only used when new Logins are created, the effective use language is defined per Login / or per session;

    SELECT Name, default_language_name
    FROM sys.server_principals


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Joan Ferrer Friday, August 31, 2018 11:25 AM
    Friday, August 31, 2018 10:22 AM
  • To add on to Olaf's answer, you can change default language for existing logins with ALTER LOGIN: 

    ALTER LOGIN YourLogin
    WITH DEFAULT_LANGUAGE = british;
    


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Joan Ferrer Friday, August 31, 2018 11:24 AM
    Friday, August 31, 2018 10:37 AM
  • One more thing

    Regarding your date issue, the reason is you're not using a language neutral format

    A format like is not language neutral whereas format like is neutral as seen from below illustration

    set language British
    go
    
    select cast('2018-02-01' as datetime) as d1,cast('20180201' as datetime) as d2
    
    set language us_english
    go
    
    select cast('2018-02-01' as datetime) as d1,cast('20180201' as datetime) as d2
    
    
    /*
    Output
    -------------------------------------------------------
    
    d1	                d2
    -----------------------------------------------
    2018-01-02 00:00:00.000	2018-02-01 00:00:00.000
    
    
    
    d1	                d2
    ---------------------------------------------------
    2018-02-01 00:00:00.000	2018-02-01 00:00:00.000
    
    
    
    */

    d2 preserves the same value always whereas d1 value flips based on the language setting

    So try to use language neutral format always to avoid relying upon the system language regional settings


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, August 31, 2018 10:41 AM
  • I have detected a difference between development and production instances of SQL Server. The difference is that the string '2018-02-01' in development that is in US English, 2 is the month and 1 is the day. In Production that is British english 2 is the day and 1 is the month. I want to change the language in development so that production and development works in the same way.

    Makes sense.

    At the database level is the difference. So I have changed the default language in Develpment to British english

    But wait! Do you really have a dependency so that 2018-02-01 is interpreted as Jan 2nd? That's a very odd format.

    Furhermore, the interpretation YYYY-DD-MM only applies to datetime and smalldatetime. For date and datetime2, the interpretation ignores the language and dateformat setting and it is always YYYY-MM-DD:

    set language British
    go
    select cast('2018-02-01' as datetime) as datetime,        cast('2018-02-01' as date) as date,
           cast('2018-02-01' as datetime2) as datetime2
    go

    If you really have dependency on the interpretation YYYY-DD-MM, there is all reason to get away from it, because it is bound to cause confusion at some point.

    Friday, August 31, 2018 11:17 AM