SQL Server: The importance of the default language during a login migration

Few weeks ago, I perform a migration of a database and the associate logins.
After few days, the customer contact me for a error about “conversion of a varchar data type to datetime data type” on this application.
The error was an log4net:exception and without more explanation:

For the migration, I perform a backup/restore, the collation on both servers is the same and I don’t figure out what is the problem….

I searching the difference between both environment with the server configuration, the instance configuration and the database configuration.
The last point was the SQL Login configuration and I see that the default language was not the same.
The source has German-Deutsch and destination has us_english because my login migration script does not take care on the language….

To be sure to have the answer to this case, I tested it on my lab…

I create a simple database Test_DefLang  and a simple table test with text and datetime datatype:

I create 2 SQL Logins for this databases with the role db_owner.
The only difference between both login is the default language:

I connect with the SQL Login Test_DefLang_EN and try different insert with date format:

As you can see in the screenshot, we have 3, the message:

Msg 242, Level 16, State 3, Line 7

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

This message comes when the date has a format “dd mm yyyy” with a separator.
The format “yyyy mm dd” with the same separator is inserted in the table without problem:

Now, I do the test with the German default language Login Test_DefLang_DE with the same insert script:

As you can see with this login (Default Language in German), it’s the opposite here.
The error message comes when the date has a format “yyyy mm dd” with a separator.
The format “dd mm yyyy” with the same separator is inserted in the table without problem:

Now, you see the importance of the default language in simple example and the problem & error that you can have if you don’t take care during a migration.

I hope this experience can help you and now, by dbi services, we adapt our login migration script to be sure to not have this case anymore.

Thanks also to my colleague Steven Naudet for giving me the clue to find the solution to this problem.