Hello I have a MySQL query like:
UPDATE Table_name SET Value= DATE_FORMAT(Date, '%d-%m-%Y %H:%i:%s') WHERE id = 3
It results in an error message "message": "Data truncation: Truncated incorrect datetime value: '1970-01-01T00:00:00Z'"
Is there a way to transform Date to '1970-01-01 00:00:00'? Without the T and Z
Best regards
The DATE_FORMAT function converts from DATE, DATETIME or TIMESTAMP datatype to a string representation. The first argument is the value, the second argument is the pattern to interpret the string.
To convert a string representation into a date value, we use STR_TO_DATE function. A simple example, converting the string containing the T and Z into a MySQL DATE value
SELECT STR_TO_DATE( '1970-01-01T00:00:00Z' , '%Y-%m-%dT%TZ')
It looks like the error is reported from the implicit conversion, from string to date, with the string value in an unrecognized format. With some string formats, we don't have to use the STR_TO_DATE function. We only have to pull that function out when the string is in a format unrecognized by MySQL (like that string with the T and Z).