Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Getting Data truncation: when trying to update a datetime string in ISO 8601 format

The table A contains the field purchasedate which is of type varchar and contains values with ISO 8601 format for example 2024-03-07T20:18:10Z. I now try to update this value by subtracting one hour

To do this I try the following:

UPDATE A
SET purchasedate = DATE_FORMAT(DATE_SUB(CONVERT(purchasedate, DATETIME), INTERVAL 1 HOUR), '%Y-%m-%dT%H:%i:%sZ')
WHERE year = '2024';

When I execute the query I get this message: [22001][1292] Data truncation: Truncated incorrect datetime value: '2024-02-07T14:38:06Z'

On the other hand, in case it is relevant, when doing a SELECT query I get the expected results

SELECT DATE_FORMAT(DATE_SUB(CONVERT(purchasedate, DATETIME), INTERVAL 1 HOUR), '%Y-%m-%dT%H:%i:%sZ') AS new_date
FROM A
WHERE year = '2024' order by date_entered desc limit 10;

Thanks for your assistance

Tags:

2

Answers


  1. 0

    Fix this by using the STR_TO_DATE function to explicitly specify the input format of the varchar date string.
    Below is an updated version of your UPDATE query:

    UPDATE A
SET purchasedate = DATE_FORMAT(DATE_SUB(STR_TO_DATE(purchasedate, '%Y-%m- 
%dT%H:%i:%sZ'), INTERVAL 1 HOUR), '%Y-%m-%dT%H:%i:%sZ')
WHERE year = '2024';

    This will help prevent the data truncation issue you are facing.

    Login or Signup to reply.
  2. 0

    MySQL’s datetime format doesn’t support the trailing ‘Z’ of the ISO format.

    mysql> select date('2024-02-07T14:38:06Z');
+------------------------------+
| date('2024-02-07T14:38:06Z') |
+------------------------------+
| 2024-02-07                   |
+------------------------------+
1 row in set, 1 warning (0.02 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect datetime value: '2024-02-07T14:38:06Z' |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)

    If you strip the last ‘Z’ it works without triggering a warning:

    mysql> select date(trim(trailing 'Z' from '2024-02-07T14:38:06Z'));
+------------------------------------------------------+
| date(trim(trailing 'Z' from '2024-02-07T14:38:06Z')) |
+------------------------------------------------------+
| 2024-02-07                                           |
+------------------------------------------------------+
1 row in set (0.01 sec)

    I suggest you store datetime values using MySQL native DATETIME type, not VARCHAR.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search