1

Topic: To suppress an error: [22001 [1292] Data truncation: Incorrect datetime value:

Actually a subject - "AS"?
The task to cut from a text box of 2 dates on an anticipating key and to save as separate fields to the same record. A format of dates "what got", type dd/mm/yyyy, dd-mm-yyyy, that is some characters can be a separator, and dates can be even such dd/m/yy.
In general made the simplified variant which the part of dates does not find and in request

SELECT id;
@pos: = LOCATE (' Period of validity of financial support ', paymentInfo) AS pos;
@len: = CHAR_LENGTH (' Period of validity of financial support with: ') AS len;
@fromDay: = SUBSTRING (paymentInfo, @pos + @len, 2) AS finFromDay;
@fromMonth: = SUBSTRING (paymentInfo, @pos + @len+3, 2) AS finFromMonth;
@fromYear: = SUBSTRING (paymentInfo, @pos + @len+6, 4) AS finFromYear;
DATE (CONCAT_WS ("-", @fromYear, @fromMonth, @fromDay)) AS finFromDate;
@toDay: = SUBSTRING (paymentInfo, @pos + @len + 14, 2) AS finToDay;
@toMonth: = SUBSTRING (paymentInfo, @pos + @len + 17, 2) AS finToMonth;
@toYear: = SUBSTRING (paymentInfo, @pos + @len + 20, 4) AS finToYear;
DATE (CONCAT_WS ("-", @toYear, @toMonth, @toDay)) AS finToDate
FROM
myTable
;

Not recognized dates quite normally quit as NULL
However, as soon as I will transform request in UPDATE I receive this error.
How to suppress wrong  dates and to replace on NULL values? (For a long time did not potter with parsing of texts on a cheekbone)

2

Re: To suppress an error: [22001 [1292] Data truncation: Incorrect datetime value:

So:

UPDATE
myTable AS p
JOIN (
SELECT id;
@pos: = LOCATE (' Period of validity of financial support ', paymentInfo) AS pos;
@len: = CHAR_LENGTH (' Period of validity of financial support with: ') AS len;
@fromDay: = SUBSTRING (paymentInfo, @pos + @len, 2) AS finFromDay;
@fromMonth: = SUBSTRING (paymentInfo, @pos + @len+3, 2) AS finFromMonth;
@fromYear: = SUBSTRING (paymentInfo, @pos + @len+6, 4) AS finFromYear;
DATE (CONCAT_WS ("-", @fromYear, @fromMonth, @fromDay)) AS finFromDate;
@toDay: = SUBSTRING (paymentInfo, @pos + @len + 14, 2) AS finToDay;
@toMonth: = SUBSTRING (paymentInfo, @pos + @len + 17, 2) AS finToMonth;
@toYear: = SUBSTRING (paymentInfo, @pos + @len + 20, 4) AS finToYear;
DATE (CONCAT_WS ("-", @toYear, @toMonth, @toDay)) AS finToDate
FROM myTable
) AS t ON t.id = p.id
SET
p.finFromDate=t.finFromDate, p.finTODate=t.finToDate
;

In spite of the fact that separately taken SELECT fulfills without error reports, replacing curve dates on NULL.
Whether it is possible to make something or only "pens" (on the client)?

3

Re: To suppress an error: [22001 [1292] Data truncation: Incorrect datetime value:

Show descriptions of this field in DDL tables.

4

Re: To suppress an error: [22001 [1292] Data truncation: Incorrect datetime value:

Akina;
In the table it is a lot of fields, but operation is conducted over one field: paymentInfo it of type text.
Adding of dates happens in the fields added in the table so:

ALTER TABLE ` myTable ` ADD COLUMN ` finFromDate ` DATE DEFAULT NULL COMMENT ' ._ ' AFTER ` idStatus `;
ALTER TABLE ` myTable ` ADD COLUMN ` finToDate ` DATE DEFAULT NULL COMMENT ' ._ ' AFTER ` finFromDate `;

5

Re: To suppress an error: [22001 [1292] Data truncation: Incorrect datetime value:

P.S.
In general the question solved on the client, but remains not clear how to suppress such error. Interesting on the future.
The most amusing that separately taken SELECT (subquery) does not produce any errors, and fairly gives NULL there where could not transform to date or there where it appeared @pos = 0. The insertion of such check in request eliminates a part of errors, but only a part. Curve formats of dates all the same produce this error.

6

Re: To suppress an error: [22001 [1292] Data truncation: Incorrect datetime value:

Arhat109 wrote:

remains not clear how to suppress such error

If to consider that error messages are specially invented that about them it became known and it was possible to react to them, it becomes clear that it is necessary not to suppress an error, and to correct the code so that it did not arise. Most simple in this case the decision - after conversion to check up the obtained value on compliance to a date template (RLIKE approaches), and depending on  or to update only those records which transited this check, or the values which have done not transited check explicitly to replace on NULL.

7

Re: To suppress an error: [22001 [1292] Data truncation: Incorrect datetime value:

Akina;
Thanks, in general it too one of "suppression" methods., as it was interesting. smile

8

Re: To suppress an error: [22001 [1292] Data truncation: Incorrect datetime value:

Well that is

SET p.finFromDate=CASE WHEN t.finFromDate RLIKE ' a template of a literal of date ' THEN t.finFromDate ELSE NULL END

Or it is stupid

WHERE t.finFromDate RLIKE ' a template of a literal of date'

9

Re: To suppress an error: [22001 [1292] Data truncation: Incorrect datetime value:

Arhat109 wrote:

it too one of "suppression" methods

Is not present.
Suppression - the error remained.
Correction - the error is eliminated.