About the char data type to a datetime data type conversion error
There are some times where database applications give the following error when trying to convert strings to the datetime format in SQL Server: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
How to resolve the above conversion error
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.
Approach 1: Modify your T-SQL Scripts & Application Code
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.
Approach 2: The Default Language Setting
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]; GO 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]; GO ALTER LOGIN "LOGIN_NAME" WITH DEFAULT_LANGUAGE = us_english; GO
* Note 1: You have to change the “LOGIN_NAME” string to the desired login name for which you would like to change its default language setting.
* Note 2: You can also change this setting through SQL Server Management Studio.
As a concluding remark, the safest way to handle date format 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!
Learn more tips like this! Get the Online Course!
Check our online course titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).
Learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals (SQL Database for Beginners)
- The Philosophy and Fundamentals of Computer Programming
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database
- SQL Server 2019: What’s New
- Entity Framework: Getting Started (Ultimate Beginners Guide)
- How to Import and Export Data in SQL Server
- Get Started with SQL Server in 30 Minutes
- A Guide on How to Start and Monetize a Successful Blog
Related Error Messages and ways to Resolve them:
- Operating System Error 170 (Requested Resource is in use)
- There is no SQL Server Failover Cluster Available to Join
- Setup failed to start on the remote machine. Check the Task scheduler event log on the remote machine.
- SQL Server 2008 R2 Service Pack Installation Fails – Element not found. (Exception from HRESULT: 0x80070490)
- Could not load file or assembly ‘Microsoft.SqlServer.Smo, Version=10.0.0.0, …
- The operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group
- There is not enough space on the disk. (mscorlib)
- A network-related or instance-specific error occurred while establishing a connection to SQL Server
- … more SQL Server troubleshooting articles
Check out our latest software releases!
Subscribe to our newsletter and stay up to date!
Easily generate snippets with Snippets Generator!
Secure your databases using DBA Security Advisor!
Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018) and a Udemy Instructor. He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks 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). Moreover, Artemakis teaches on Udemy, you can check his courses here.