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 :
And run dbcc useroptions
Again, we will see the same output as shown below :
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
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!