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

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

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
There are some times where database applications give the above error when trying to convert strings to the datetime format in SQL Server.

This error is related to the default language and consequently the dateformat used by the corresponding SQL Server login.

There are two approaches for avoiding/resolving this issue. The first approach is to include the necessary logic in your database application in order not to depend on the default language setting, but use the ISO date format which is: yyyy-mm-dd hh:nn:ss.

The second approach is to change the default language setting for the specific SQL Server login to “us_english”.

By executing the following script in SQL Server you get a list with all the logins and among others, the default language names used by these logins. The relevant column name in the results of this script is the “DefLangName”:

–Query that changes the default language to “us_english” for a given SQL Server login:
use [master]
EXEC sp_helplogins
GO

If the default language name for the specific login is different than “us_english”, and your application’s code does not explicitly handle these date-conversion issues, there is the possibility of getting the conversion error when executing your application. You can easily change this setting to “us_english” by executing the below script:

–Query that changes the default language to “us_english” for a given SQL Server login:
use [master]
ALTER LOGIN “LOGIN_NAME” WITH DEFAULT_LANGUAGE = us_english;
GO

* You have to change the “LOGIN_NAME” string to the desired login name for which you would like to change its default language setting.

* You can also change this setting through SQL Server Management Studio.

Concluding, the safest way to handle dateformat data in database applications, is to setup the default language setting for the relevant SQL Server login to “us_english” and use parameterized queries in your source code. To this end, the date formatting will be automatically handled thus avoiding possible conversion errors.

Hope this helps.


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.

One thought on “The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

  1. f2KFaMh.qc5_Yyqy0wfV3TTVGk46wg--

    Just as a sidenote to this, i'm running SQL Server 2005 SP2 (with the language of the sql account set to British English). I had issues around using the ISO format YYYY-MM-DD. I got the "conversion of char data type" exception with that format when the day was > 12. When changing the language of the sql account to Engligh (us_english) the same query went through fine. However, when i changed my code to format the date into YYYYMMDD the query worked with both languages, so i don't know if this is a SQL bug or what, but i'm using the latter format now.