"Arithmetic overflow error converting expression to data type datetime.Native error: 8115 SQLState: 22003" error message in Microsoft SQL

Problem Description: Following error message may be thrown by some applications that uses date formats:

"Arithmetic overflow error converting expression to data type datetime.Native error: 8115 SQLState: 22003"

A application that was working properly earlier can certainly start to throw this error message after some changes were made to the environment. In my usercase a database was moved from one SQL server to another SQL server. Problem started to occur on 13th of month, problem was present till 1st of next month. Problem will come back on 13th of next month.

Cause: Date formats used by service account can cause problem while converting data that is related to calander dates. In my usercase default language for Service Account within Microsoft SQL Server has been changed from one format to another e.g. from English (US_English) to British.


Solution: There are different solutions for it but if a application is already wrote and you only want to resolve the problem of error message following is what you can try.

Note: These steps are written for MS SQL Server 2005 you might want to change it a little for other versions of MS SQL Server

1. Log on to SQL server that hosts databases in question.
2. Launch 'SQL Server Management Studio'.
3. When prompted, log on using the credentials that will allow you to make changes to the SQL server configuration.
4. In the left pane of 'Microsoft SQL Server Management Studio' expand 'Security' section.
5. Further expand 'Logins' section.
6. Select Service Account and right click on it.
7. Select 'Properties'
8. On 'Login Properties' screen on right side of the pane check 'Default Language' section.
9. If 'Default Language' is set to British change it to English (or vise versa depending on your needs)
10. Select 'OK'

Language and dateformats specific for a service account can also be checked by running 'dbcc useroptions' query analyzer while logged in using service account .

Caution: Please also check with the vendor of the application on what date format is preffered by the application as making changes to date format settings can cause problem with the data that will be stored by service account into the SQL tables.