Sql-server – Two Databases on same server handle date differently

date formatsql serverssms

I have two databases on the same server, one is a backup of the other from a few weeks ago.

I have a third party application that has started misbehaving. It logs the LastLoginDate of a particular user when they log on using the following command.

Update DBO.theTable Set LastLoginDateTime = '2015/10/27 15:14:34' 
Where UserName = 'theUser'

Now in the live database this works fine, yet when I run the same command on the Backup database I get the following error

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

This looks like a simple date format error, but I can't find out how to rectify it. From what I understand the language of the database is set at the server level, so this should be the same. Also, the same user at the server level is being used, so again, the Language of that user should not come into play.

As mentioned above, it's a third-party app so I can;t change the underlying SQL to be a different (more universal) date format, so I'll have to handle it at the database level.

Does anyone have any ideas on what I should be checking beyond the above to fix this?

UPDATE

I have worked out a fix to this problem but would still appreciate advice on why the databases are behaving inconsistently. The two databases share a User 'AdminUser'. There are also user accounts set at the server level such as 'AppUser'. 'AppUser' has experienced no problems while I have been investigating this issue. They are set to use British English language

enter image description here

Yet when they log on to the database the database context switches to US English (as captured using SQL Server Profiler)

enter image description here

As a result of this I have switch the AdminUser to have their default Language set to 'English', and now it works as expected

enter image description here

So when I execute the query posted at the top I get the result

(1 row(s) affected)

While the application is now working as expected, it surely can;t be right to have some users set to 'British English', and some set to (US) 'English' to achieve the same behaviour! Any ideas are welcomed.

Best Answer

If I understand the problem correctly, when both users have British as default language, one user's request goes through fine because the language setting changes automatically to us_english. But for another user, the language setting doesn't change automatically, hence the error.

The BOL states following regarding default language and how it can be overridden.

The default language for a login can be overridden by using CREATE LOGIN or ALTER LOGIN. The default language for a session is the language for that session's login, unless overridden on a per-session basis by using the Open Database Connectivity (ODBC) or OLE DB APIs. Note that you can only set the default language option to a language ID defined in sys.syslanguages (0-32). When you are using contained databases, a default language can be set for a database by using CREATE DATABASE or ALTER DATABASE, and for contained database users by using CREATE USER or ALTER USER. Setting default languages in a contained database accepts langid value, the language name, or a language alias as listed in sys.syslanguages.

Based on this the following can affect the language setting.

  • Database default
  • User default
  • DSN setting, connection strings, etc..
  • SET LANGUAGE in session

If underlying statements can't be changed, is there a setting that can affect it in the third party app? Or is there are trigger for a certain user login? Though I believe it's one or a combination of 2 or more in the above list....

Related Question