For example, there are two tables like this:
1.original table
date_str |
---|
2016-02 |
2016-02-02 |
2.new table(Created based on the original table, an additional is date field is added)
date_str | is_date |
---|---|
2016-02 | |
2016-02-02 |
date_str
in the original table should be in date format, but the actual field type is varchar, and the data stored is also a variety of, not only date format strings, but also other formats (for example, 123, abc, etc.).
I will now extract the date_str
field from the original table and insert it into the new table. Check whether it is in date format when inserting, and if not, fill DATE_FORMAT
in the is_date
field
My SQL statement is:
INSERT INTO new_table ( date_str, is_date )
SELECT
ot.date_str AS date_str,
CASE
WHEN DATE_FORMAT( ot.date_str, '%Y-%m-%d' ) IS NULL
THEN 'DATE_FORMAT'
END AS is_date
FROM original_table ot
If you execute this SQL, you will get an error: Incorrect DateTime value: ‘2016-02’
I looked it up on the Internet and needed to adjust the MySQL configuration file my.cnf
to remove the sql_mode=STRICT_TRANS_TABLES from the configuration file, but I didn’t want to do so, because it would affect other tables.
2
Answers
You can temporarily set the
sql_model
attribute of the mysql configuration filemy.cnf
, removeSTRICT_TRANS_TABLES
, and then add it back after the sql is executed. The sql is:Or use regular expressions.
Use STR_TO_DATE instead of
DATE_FORMAT
. That method parses strings into dates and returns NULL if the string can’t be parsed.Dates in all databases except SQLite are binary values, they have no format. Most databases will parse strings into dates implicitly though. If the parse fails, the entire query fails.
DATE_FORMAT itself expects a date value to format into a string.
The only reason the query works at all is that MySQL implicitly parses the string into a date before passing it to
DATE_FORMAT
. When the implicit cast fails, the entire query fails. At that point,DATE_FORMAT
hasn’t been called yet.This example, also shown in this dbFiddle, creates a table with some text fields and calculates the
is_date
fields.A query with
STR_TO_DATE
returns the following resultTo update the table we need to use
IGNORE
.STR_TO_DATE
generates a warning if the value doesn’t match. SELECT doesn’t care but UPDATE/INSERT will convert the warning to an error :