-1

i use datetimepicker to save date only but when i save it to the database, it always have time with it. I custom format datetimepicker to date only dd/MM/yyyy but still gets the error it has time with it.

BTW, Date column datatype is Date

here's my save code

dtpDate.Value = Date.Now


    Dim cmd As MySqlCommand = MySqlConn.CreateCommand
                        cmd.CommandText = String.Format("INSERT INTO teeth (Date)" &
                                                    "VALUES ('{0}')",
                                                    dtpDate.Value)
user5567987
  • 167
  • 1
  • 12
  • `Date` is a reserved word and a bad choice for a column name. The `DTP.Value` is a `DateTime` type and will **always** include a time, formatting just removes the time from view. MySQL should be able to convert it to `Date` though. Perhaps if you passed it as a Date parameter rather than string... – Ňɏssa Pøngjǣrdenlarp Feb 12 '16 at 17:03
  • 1
    DateTimePicker.Value is a **DateTime** type, the display format used to show your date has nothing to do with the value of the picker. You could use dtpDate.Value.Date to get the date part with the time set to 00:00:00, however being it inserted in a string using the Format method it will be converted in a string using the locale settings and this doesn't bode well with your database. Use parameters – Steve Feb 12 '16 at 17:03
  • 1
    *Don't* use string concatenation to pass values. Use a parameterized query. Otherwise, not only do you get errors due to unexpected string formatting as in this case, but you open yourself to string injection attacks – Panagiotis Kanavos Feb 12 '16 at 17:06
  • To strip off the time portion in a DatePicker, use the `Date` method on the value. This will replace the time with midnight. – Martin Soles Feb 12 '16 at 20:25

1 Answers1

1

This should do it:

dtpDate.Value = Date.Today
Dim cmd As New MySqlCommand("INSERT INTO teeth (Date) VALUES ( @Date )", MySqlConn)
cmd.Parameters.Add("@Date" MySqlDbType.Date).Value = dtpDate.Value

Note this also fixes a huge security issue in the old code.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • You should note that Date.Today will set the time portion to midnight. Date.Now uses the current time. To remove the time portion from any arbitrary Date variable in .net, use the `Date` method. An example `Dim x As Date = Date.Now.Date` will strip off the current time and replace it with midnight. – Martin Soles Feb 12 '16 at 20:23
  • @MartinSoles The `.Date` property still returns a `DateTime` value with a time of midnight. `Date.Now.Date` is effectively the same thing as `Date.Today`. – Joel Coehoorn Feb 12 '16 at 21:38
  • Yes. I was just using that since it was a fast way to generate a value with some time component in it. A better example would be `Dim x As New Date(2016, 2, 15, 18, 3, 2, 900)' and then showing how to reset the time to midnight. – Martin Soles Feb 15 '16 at 14:24