   [C# and SQL] Conversion failed when converting date and/or time from character string
  Technology: Microsoft Visual Studio 2010 Professional, Microsoft SQL Server 2008Table: tblCustomersFields causing problems: HolidayStart, HolidayEnd both defined as a date.This is a really strange bug that has been frustrating me for awhile I've tried googling about but most solutions are using different languages or completely different SQL statements. It's also strange as in a completely different function where I insert a new record to my tblCustomers I pass it a brand new DateTime that I make on the spot which works fine. The only difference is I use a SqlParameter when adding a customer, I tried using this with the modification of the Holiday start and end dates however I still got the same error.This is the code which handles the button press that results in the messagebox containing the error.private void btnAddHoliday_Click(object sender, EventArgs e)   {  try  { if (calendarHoliday.SelectionStart < DateTime.Today) {throw new ApplicationException("Holiday begins before today"); } if (calendarHoliday.SelectionEnd < DateTime.Today) {throw new ApplicationException("Holiday ends before today"); } custDB.AddHoliday(customerID, calendarHoliday.SelectionStart, calendarHoliday.SelectionEnd);  }  catch (Exception ex)  { MessageBox.Show(ex.Message);  }   }The function AddHoliday is defined as follows:public void AddHoliday(int id, DateTime HolidayStart, DateTime HolidayEnd)   {  // Create sql statement to Update the fields HolidayStart and HolidayEnd  string sql = string.Format("UPDATE tblCustomers SET HolidayStart = '{0}', HolidayEnd = '{1}' WHERE CustomerID='{2}'",   HolidayStart.ToShortDateString(), HolidayEnd.ToShortDateString(), id);  SqlCommand cmd = new SqlCommand(sql, this.sqlCn);  cmd.ExecuteNonQuery();   }I set a break point when the code hits the line custDB.AddHoliday() and stepped through and pulled the value of the string generated:sql = "UPDATE tblCustomers SET HolidayStart = '24/06/2010', HolidayEnd = '25/06/2010' WHERE CustomerID='1'"I have tried things such as changing the type to datetime/datetime2 on the table and tried using CONVERT(DATETIME, '{0}')Tried CONVERT(DATE, '{0}') which I think resulted in an error to do with integersPassing a straight datetime without the ToShortDateString() into the string but nothing seems to solve this error.Edit: Due to time constraints I have since "fixed" the error by creating a new link table for holidays which is fully working although still would be interested in knowing what others think of this error.[Edited by - ExcessNeo on May 9, 2010 10:43:00 PM]
Quote:sql = "UPDATE tblCustomers SET HolidayStart = '24/06/2010', HolidayEnd = '25/06/2010' WHERE CustomerID='1'"Try to change the format from 'dd/mm/yyyy' to 'yyyy-mm-dd' or 'yyyy-mm-dd 00:00:00' if using datetime.;  Yeah, it'll definitely be down to the dateformat. I often have to ensure that I set dateformat ymd when dealing with dates in the YYYY-MM-dd format.   ;  And make sure to make the turkey test: http://stackoverflow.com/questions/796986/what-is-the-turkey-test.   ;  Also, change your code to look more like:public void AddHoliday(int id, DateTime HolidayStart, DateTime HolidayEnd)   {  // Create sql statement to Update the fields HolidayStart and HolidayEnd  string sql = "UPDATE tblCustomers SET HolidayStart = @HolidayEnd, HolidayEnd = @HolidayEnd WHERE CustomerID = @CustomerId";  SqlCommand cmd = new SqlCommand(sql, this.sqlCn);  cmd.Parameters.Add("@HolidayStart", SqlDbType.Date).Value = HolidayStart;  cmd.Parameters.Add("@HolidayEnd", SqlDbType.Date).Value = HolidayEnd;  cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = id;  cmd.ExecuteNonQuery();   }It's better practice to parameterise queries like this. It can also help avoid conversion issues too as you start being more explicit.   ;  Cheers guys will bookmark the Turkey test for future reference :p   