[SOLVED] MySQL DATE_FORMAT from '1970-01-01T00:00:00Z' to '1970-01-01 00:00:00'

MySQL DATE_FORMAT from '1970-01-01T00:00:00Z' to '1970-01-01 00:00:00'

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

Solution

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).