I get this error:
Unable to convert MySQL date/time value to System.DateTime
while I am trying to fetch the data from a MySQL database. I have the date datatype in my MySQL database. But while retrieving it into my datatable, it get the error above.
How can I fix this?
Here is the query to convert from datetime to date in MySQL. mysql> select cast(ArrivalDatetime as Date) as Date from ConvertDateTimeToDate; The following is the output.
MySQL Column Types Range: 1970-01-01 00:00:01 to 2038-01-19 03:14:07 for TIMESTAMP ; 1000-01-01 to 9999-12-31 for DATETIME . Time zones: TIMESTAMP values will be converted to UTC for storage and from UTC for retrieval, which can lead to reading different values.
MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
MySQL uses yyyy-mm-dd format for storing a date value. This format is fixed and it is not possible to change it. For example, you may prefer to use mm-dd-yyyy format but you can't. Instead, you follow the standard date format and use the DATE_FORMAT function to format the date the way you want.
You must add Convert Zero Datetime=True
to your connection string, for example:
server=localhost;User Id=root;password=mautauaja;Persist Security Info=True;database=test;Convert Zero Datetime=True
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With