How to change Date Format after installing SQL server

How to change Date Format after installing SQL server

Rate This
  • Comments 10

In the below blog, I am going to share my experience on one of the unique case which I have worked couple of days back.

The issue will arise if you have installed SQL with the default collation and your machine is using English (US). Now say for example you want to do business in a country where the default language is other than English (US) and default date format is “DD-MM-YYYY” instead of MM-DD-YYYY”.  Moreover, your application have already been developed and the code cannot be changed to accept the date format in “MM-DD-YYYY”.

You may get the following error message when you are trying to update the table from SSMS directly.

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

If you run  dbcc useroptions you get the below output :

Set Option                                                                                                                       Value

-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------

textsize                                                                                                                         2147483647

language                                                                                                                         us_english

dateformat                                                                                                                       mdy

datefirst                                                                                                                        7

lock_timeout                                                                                                                     -1

quoted_identifier                                                                                                                SET

arithabort                                                                                                                       SET

ansi_null_dflt_on                                                                                                                SET

ansi_warnings                                                                                                                    SET

ansi_padding                                                                                                                     SET

ansi_nulls                                                                                                                       SET

concat_null_yields_null                                                                                                          SET

isolation level                                                                                                                  read committed

(13 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

From the above output we can see that the dateformat is “mdy”.

Now you go to the advance option and change the option to British English as in the below screen shot :

image

And run dbcc useroptions

Again, we will see the same output as shown below :

Set Option                                                                                                                       Value

-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------

textsize                                                                                                                         2147483647

language                                                                                                                         us_english

dateformat                                                                                                                       mdy

datefirst                                                                                                                        7

lock_timeout                                                                                                                     -1

quoted_identifier                                                                                                                SET

arithabort                                                                                                                       SET

ansi_null_dflt_on                                                                                                                SET

ansi_warnings                                                                                                                    SET

ansi_padding                                                                                                                     SET

ansi_nulls                                                                                                                       SET

concat_null_yields_null                                                                                                          SET

isolation level                                                                                                                  read committed

(13 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Hence this means that even we have changed the server option to British English, we are still seeing the old values for dbcc useroptions.

We get the above value, even after we have changed the language setting on the OS level.

Now if we use set dateformat dmy

And do an insert in the table we are able to do the changes, but not through the application and we can’t incorporate “set” option in the connection string. So now the question is, how can we achieve our desired result. The answer is a bit tricky :

Ø Change the OS settings.

Ø Change the server setting to British English.

Ø Now create a new user and use dbcc useroptions

Ø Now you would be seeing the option have changed.

Hence, if you have changed the settings then re-create the users which are related to the application and you would be able to use the new settings.

Additional Information:

http://msdn.microsoft.com/en-us/library/ms189751.aspx

 

Gaurav Srivastava

SE, Microsoft SQL Server

 

Reviewed by:

Nickson Dicson

Tech Lead, Microsoft SQL Server

Leave a Comment
  • Please add 6 and 7 and type the answer here:
  • Post
  • I have this issue .

    We are already having application in asp.net . Now we have two sets of clients logging to the application . One require mdy format other dmy format both in search and display .

    What is the best way to implement with minimal changes to the application ?

  • The above blog discuss about the workaround which you may implement if there are no workaround and you want to change the format at the server level. If you are still in the design phase then you can have this implemented at the application level.

    However, as mentioned that if a user is created with a pirticular date format it will retrive the data in the same format from the server, whatever be the format you choose to store the data at the server level.

    You may try the steps which is mentioned and create the logins for the application under the desired format and that should help.

    Cheers,

    Gaurav

  • Rather than recreating the users, you can

    go into the top-level Security tab, Logins, and right-click the individual user account.

    At the bottom of the default (General) page, set the Default Language to whatever (in my case British English).

    Log the user off & back in - result!

  • Very good "Steve",

    Your trick solved my problem.

  • Very good "Steve",

    Your trick solved my problem.

    Thanks

  • Hi,

    I am not able to retrive Thai date from getdate() function, my DataBase and Server collations are in Thai_CI_AS, and default language set to Thai.

    Please help me urgently, how to get thai date from sql server getdate() function.

    Regards,

    Vamshi

  • Hi guys,

    Sorry this request is a little latter than the post but I have an issue to resolve here.

    I wonder if you can help me here.

    I have a 2012 Enterprise and my users want to get UK format date from select getdate().  At the moment it returns 2014-03-06 18:18:53.880. I have done

    dbcc useroptions

    set dateformat dmy

    created a new user (with the default language of British English)

    select getdate() still return the format above.

    My head user is reluctant to use select convert(char,getdate(),...) in his queries

    IS there anything else that I need to do?

    Regards,

    Timothy

  • I did not need to recreate the user but simply modified it and my problem was solved.

    Here is what I did:

    USE [master]

    GO

    EXEC sys.sp_configure N'default language', N'0'

    GO

    RECONFIGURE WITH OVERRIDE

    GO

    ALTER LOGIN [myuser] WITH DEFAULT_LANGUAGE=[us_english]

    GO

  • Thanks very much. I am having some challenges. I want to permanently change the default week start from Sunday to Saturday.

    Our company week runs from Saturday through Friday.

    I have used SET DATEFIRST 6; but is not working.

    Please,help

  • Only sa (administrator) can change default_language

Page 1 of 1 (10 items)