How to convert varchar to datetime

Question

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

Amit Gupta · 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)

Rakesh · Answer

select getdate() date into varchar SELECT CONVERT(VARCHAR(10) GETDATE() 103) varchar to datetime SELECT CONVERT(datetime GETDATE() 108)

Amit Singh · Answer

hey 1.firstwe 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 Moizuddin · Answer

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