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?
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
Yet when they log on to the database the database context switches to US English (as captured using SQL Server Profiler)
As a result of this I have switch the AdminUser to have their default Language set to 'English', and now it works as expected
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.