I have saved dates from datetimepicker to sql in my windows form app with the format (dd-MM-yyyy). To edit date, I want to first fetch saved date and show in the datetimepicker control. Like if 28-03-2000 is saved in db then datetimepicker should show this same date and should understand the format dd-MM-yyyy. The code with which I am trying to do is as follows
birthday_search.Text = Convert.ToDateTime(reader["Birthday"]).ToString("dd-MM-yyyy");
birthday_search is the name of datetimepicker. This gives error “String was not recognized as a valid datetime” because its itself picking the format MM-dd-yyyy which is turning out to be wrong.
Advertisement
Answer
The problem is the type of the field Birthday.
You say you have it as a NVARCHAR column. This is wrong for many reasons.
First the database is not able to do appropriate searches on that field. For example: is “28-12-2000” before “14-01-2001”? If you look at it as a string NO because 1 is before 2, but if you look at it as a Date then YES.
Then you will have troubles converting that value back to a datetime variable when loading it. The conversion will assume the CultureInfo of the machine where the conversion occurs. This could work correctly or not depending on the input. For example on my machine the string “04-06-2017” will be converted in the 4th of June, but on a machine where the culture info is set to MM-dd-yyyy the same string will be parsed to the 6th of April.
So you just need to change that field to a datetime type, store values as DateTime variables and read them back with
birthday_search.Value = Convert.ToDateTime(reader["Birthday"]);
or
birthday_search.Value = (DateTime)reader["Birthday"];
No formatting involved here. A field of type datetime is assigned to a property of type DateTime. The formatting to display this value is the job of the Format and CustomFormat of the DateTimePicker. Of course this code doesn’t consider the possibility of a NULL value.