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

CodePudding user response：

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

CodePudding user response：

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'):

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;

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;

fiddle