Project scenario:

This project is an old one, which is outsourced. As a result, many parts of the development do not pay much attention to the details. There is a field of date type that is designed as integer, which is easy to cause some strange problems.

Problem Description:

In the process of development, I encountered an SQL error in the execution of “invalid month”. I checked the submission record of this file and found that the file was last modified half a year ago, indicating that it was not a SQL problem. That’s the data problem.

Cause analysis:

Preliminary analysis of this issue should date, because there is a function to in SQL_ Date (), most likely a data format.
After putting all the LR_ After replacing the date field with null, we find that SQL can find out the data, which further verifies our conjecture. It’s about the date.
First of all, we suspect that there may be a very large number or a very small number in the date field, but we find that it is not.

And then I fell into meditation.

For example, in December, I doubt whether there will be any unconventional data in this field.

In order to verify this conjecture, we use substr (a, 6, 2) to cut the field, intercept the month, and find out the data of substr (a, 6, 2) & gt; 12.

Finally, find the problem. There is a data 20211301, which leads to this problem.

Solution:

Change the date of abnormal data to normal value.