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
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.
SE, Microsoft SQL Server
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.
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.
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.
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
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?
I did not need to recreate the user but simply modified it and my problem was solved.
Here is what I did:
EXEC sys.sp_configure N'default language', N'0'
RECONFIGURE WITH OVERRIDE
ALTER LOGIN [myuser] WITH DEFAULT_LANGUAGE=[us_english]
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.
Only sa (administrator) can change default_language
Very Very thanks Steve it works and solved the problem
Rather than recreating the users, you can
I really enjoyed. Thank you so much.
Mark - Brazil
Thank you very much, you really help me solved my problem, appreciate it. Thanks.
I am posting one of my query here .
While using Informatica to load data from a flat file to MS SQL database . We are having date fields ,so in informatica level we converted the flatfile date(string) to date before loading and applied the logic
which states that if its a date convert to date else null.
We are having two databases Stage and Prod . Logic worked fine in Stage database , after moving to prod we faced rejection of rows with the below error .
Database errors occurred:
Conversion failed when converting date and/or time from character string
We find the fix as to convert the date again to string and sending it to database .
which working in prod and stage . We were unable to identify the root cause for this issue but we do see that date is having a period(.) in end which was causing the rejection.
Ex: "02/07/2008 00:00:00."
Could you please help me in confirming if it is created due to some set up difference in stage and prod MS SQL databases .
Thanks all in advance
Thank you for your solution.
Its working for me