The "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value" error is a frequent problem that developers run into while working with ASP.NET apps that communicate with SQL Server. We'll talk about the causes, short-term fixes, and long-term solutions.
Overview
Date and time values are frequently required when an ASP.NET application puts data into a SQL Server database. One frequent problem occurs when attempting to pass a DateTime value as a string, which SQL Server is unable to properly read because of regional variations in date formatting. When attempting to insert DateTime, this problem usually results in the "out-of-range" error.now entered into the database as a string.
The best solution 'parameterized queries' will be presented in this post after a thorough discussion of the issue and a walkthrough of a temporary patch. This method enhances the security and maintainability of your code in addition to fixing the mistake.
The Issue: Problems with DateTime Formatting
A particular format is required by SQL Server when data, particularly DateTime values, are put into a database. An out-of-range error may occur if SQL Server is unable to properly parse the DateTime value if it is supplied as a string in an unusual format.
Here is an illustration of a situation:
The code in an ASP.NET application may dynamically construct a SQL query to add information to the ContactInfo database, which contains a DateTime field:
str1 = "insert into ContactInfo(FirstName, LastName, MobileNo, Email, Country, State, City, APIType, AppName, Reason, ReasonComment, Message, CompanyName, ContactPerson, usertype, indCompanyName, date)";
str1 += " values('-', '-', '" + Contact1.Text + "', '" +
Email.Text.Replace(" ", "") + "', '" +
drpCountry.SelectedItem.Text + "', '" +
state + "', '" +
city + "', '" +
Assestlist + "', '" +
SiteName.Text.Replace("'", "_") + "', '" +
drpApi.SelectedValue + "', '" +
usercomment.Replace("'", "_") + "', '" +
txtMessage.Text.Replace("'", "_") + "', '" +
Fname.Text.Replace(" ", "") + "', '" +
Lname.Text.Replace(" ", "") + "', '" +
usertype + "', '" +
infocompany.Text + "', '" +
DateTime.Now + "')";
In this code, DateTime.Now is concatenated directly into the SQL query. When DateTime.Now is converted to a string, it might be formatted in a way that SQL Server cannot interpret correctly, such as:
13-01-2026 10:25:40
SQL Server may expect the DateTime to be in an ISO-compliant format like:
2026-01-13 10:25:40
If the format does not match, SQL Server may throw the out-of-range error.
Temporary Fix: Use ISO 8601 Format
The quickest way to fix this issue is to format the DateTime value as an ISO-compliant string before passing it to SQL Server. This ensures that SQL Server always understands the date format, regardless of the regional settings on the server.
Modify the code like this:
string sqlDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
str1 += " values('-', '-', '" + Contact1.Text + "', '" +
Email.Text.Replace(" ", "") + "', '" +
drpCountry.SelectedItem.Text + "', '" +
state + "', '" +
city + "', '" +
Assestlist + "', '" +
SiteName.Text.Replace("'", "_") + "', '" +
drpApi.SelectedValue + "', '" +
usercomment.Replace("'", "_") + "', '" +
txtMessage.Text.Replace("'", "_") + "', '" +
Fname.Text.Replace(" ", "") + "', '" +
Lname.Text.Replace(" ", "") + "', '" +
usertype + "', '" +
infocompany.Text + "', '" +
sqlDate + "')";
By using DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), the date is formatted in a universally accepted ISO 8601 format, which SQL Server will always understand. While this works as a temporary fix, it’s not the most robust or maintainable solution.
The Long-Term Solution: Use Parameterized Queries
Although formatting the DateTime value as a string resolves the issue temporarily, the best solution is to use parameterized queries. Parameterized queries not only eliminate date conversion issues but also improve security and prevent SQL injection attacks.
Here’s how to refactor the code to use parameterized queries:
string query = @"insert into ContactInfo
(FirstName, LastName, MobileNo, Email, Country, State, City,
APIType, AppName, Reason, ReasonComment, Message,
CompanyName, ContactPerson, usertype, indCompanyName, date)
values
('-', '-', @MobileNo, @Email, @Country, @State, @City,
@APIType, @AppName, @Reason, @ReasonComment, @Message,
@CompanyName, @ContactPerson, @usertype, @indCompanyName, @date)";
Now, create the parameters:
SqlParameter[] param =
{
new SqlParameter("@MobileNo", Contact1.Text),
new SqlParameter("@Email", Email.Text.Replace(" ", "")),
new SqlParameter("@Country", drpCountry.SelectedItem.Text),
new SqlParameter("@State", state),
new SqlParameter("@City", city),
new SqlParameter("@APIType", Assestlist),
new SqlParameter("@AppName", SiteName.Text),
new SqlParameter("@Reason", drpApi.SelectedValue),
new SqlParameter("@ReasonComment", usercomment),
new SqlParameter("@Message", txtMessage.Text),
new SqlParameter("@CompanyName", Fname.Text),
new SqlParameter("@ContactPerson", Lname.Text),
new SqlParameter("@usertype", usertype),
new SqlParameter("@indCompanyName", infocompany.Text),
new SqlParameter("@date", SqlDbType.DateTime) { Value = DateTime.Now }
};
SqlHelper.ExecuteNonQuery(SQL, CommandType.Text, query, param);
Explanation of Parameterized Queries
Avoids String Concatenation: Using parameters means we no longer have to manually concatenate user inputs or DateTime values into the query string. This approach helps prevent errors and SQL injection attacks.
SQL Parameters: We pass the DateTime.Now value as a parameter with the correct type (SqlDbType.DateTime). SQL Server handles the conversion, ensuring it is always in the correct format.
Prevents SQL Injection: By using parameters, we avoid manually building the query string, which protects the application from SQL injection attacks.
Conclusion
The "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value" error occurs when SQL Server attempts to convert a string (often from DateTime.Now) into a DateTime value, and the format is not understood. The most straightforward fix is to format the date as an ISO-compliant string. However, the best practice is to use parameterized queries, which not only resolve the issue but also enhance security, maintainability, and performance.
Summary
- Temporary fix: Format DateTime as yyyy-MM-dd HH:mm:ss.
- Long-term solution: Use parameterized queries to prevent errors and SQL injection.
This approach ensures that your application works reliably across different environments and is easier to maintain in the long run.
European Best, cheap and reliable ASP.NET Core 10.0 hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.
