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

MySQL DATE_FORMAT() Data truncation: Incorrect datetime value

When I run DATE_FORMAT('test', '%W %M %Y') I get null returned.

I’m running an update to my table extras where the column is a nullable varchar, but when I run eg.

update extras
set extras.`value` = DATE_FORMAT('test', '%W %M %Y');

I get the following error:

[22001][1292] Data truncation: Incorrect datetime value: 'test'

extras.value is a varchar column with datetime values some of which are not valid dates. I want to update the column to null when the datetime is invalid ie. just a string as in this case ‘test’.

Tags:

2

Answers


  1. 0

    When the STRICT_TRANS_TABLES sql_mode is enabled, any date/time parsing error becomes a fatal error. You have to disable it or use a regex to validate the date string (which is very messy) before using it as a date.

    fiddle

    Login or Signup to reply.
  2. 0

    Check does the value is valid date with regular expression.

    Example – the most simple pattern which does not check for value validity (and allows, for example, ‘2022-25-78’, in this case the whole UPDATE will fail):

    CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  src_value VARCHAR(255),
  dst_value VARCHAR(255)
);
INSERT INTO test (src_value) VALUES ('test'), (CURRENT_DATE);
SELECT * FROM test;
    id src_value dst_value
    1 test null
    2 2022-11-22 null 
    UPDATE test
SET dst_value = DATE_FORMAT(src_value, '%W %M %Y')
WHERE src_value REGEXP '^\d{4}-\d{2}-\d{2}$' ;
SELECT * FROM test;
    id src_value dst_value
    1 test null
    2 2022-11-22 Tuesday November 2022

    fiddle

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

Back To Top
Search