How to convert varchar to datetime

Nitol Biswas

9y8.4k.NET

When I try to update the dates "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value" error is occurred. How can I fix this issue.

c# code

------------

SqlConnection cs = new SqlConnection(ConfigurationManager.ConnectionStrings["travelConnectionString"].ConnectionString);
String str = "UPDATE [tbl_training_details] SET [service_agent_id] = '" +
ddlServiceAgentList.SelectedValue + "',[training_addmission_date] = '" + txtAddmissionDate.Text + "',[training_exam_date] = '" + txtExamDate.Text + "',[training_certificate_no] = '" + txtCertificateNo.Text + "',[training_certificate_date] = '" + txtCertificateDate.Text + "',[status_id] = '" + ddlStatus.SelectedValue + "',[comment] = '" + txtComment.Text + "'WHERE [passenger_id] = " + txtPassengerName.Text;
SqlCommand sc = new SqlCommand(str, cs);
cs.Open();
sc.ExecuteNonQuery();
cs.Close();

str value I got from this code is

str = "UPDATE [tbl_training_details] SET [service_agent_id] = '0',[training_addmission_date] = '29/09/2016',[training_exam_date] = '20/10/2016',[training_certificate_no] = '5rfde',[training_certificate_date] = '2/11/2016',[status_id] = '14',[comment] = '5gfds'WHERE [passenger_id] = 10413"

Thanks

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta9y ago
Accepted answer
I personally recommend you to use parameterized query instead of concatinating the string to build your query that offers you to have a great command over building your query and makes your code clean and simple.
Have a look for creating your query
var command = new SqlCommand ();

command.Text = "UPDATE Student SET Address = @add, City = @cit Where FirstName = @fn and LastName = @add)";

//passing parameter values
command.Parameters.AddWithValue("@ln", lastName);
command.Parameters.AddWithValue("@fn", firstName);
command.Parameters.AddWithValue("@add", address);
command.Parameters.AddWithValue("@cit", city);

// execute your query
Coming to your query, as it is a DateTime datatype and you are passing it as a string, you need to convert to DateTime like

[training_addmission_date] = '" + Convert.ToDateTime (txtAddmissionDate.Text)
+2
Rakesh9y ago
select getdate()
date into varchar
SELECT CONVERT(VARCHAR(10), GETDATE(), 103)
varchar to datetime
SELECT CONVERT(datetime, GETDATE(), 108)
+1

Amit Kumar Singh9y ago

hey

1.first,we have to make make sure to filter date either with mm/dd/yyyy or with dd/mm/yyyy format, whichever works in your db.

2.The major reason for the error will be in your date in WHERE clause.

for mm/dd/yyyy format

SELECT cast(CONVERT(varchar, LoginTime, 101) as datetime) FROM AuditTrail

where CAST(CONVERT(VARCHAR, LoginTime, 101) AS DATE) <=  CAST('06/18/2012' AS DATE)

for dd/mm/yyyy format

SELECT cast(CONVERT(varchar, LoginTime, 101) as datetime) FROM AuditTrail

where CAST(CONVERT(VARCHAR, LoginTime, 101) AS DATE) <=  CAST('18/06/2012' AS DATE)

As per your update i need to extract the date only and remove the time, then you could simply use the inner CONVERT

SELECT CONVERT(VARCHAR, LoginTime, 101) FROM AuditTrail

Khaja Moizuddin9y ago
http://stackoverflow.com/questions/20838344/sql-the-conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in
http://stackoverflow.com/questions/28271414/conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in-an-out-of
http://dba.stackexchange.com/questions/86313/conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in-an-out-of
http://dba.stackexchange.com/questions/116590/the-conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in-an-out
https://support.solarwinds.com/Success_Center/Mobile_Admin/SQL_Error%3A_The_conversion_of_a_varchar_data_type_to_a_datetime_data_type_resulted_in_an_out-of-range_value
+1