Data truncation: Incorrect datetime value: '7/21/15' for column 'announced_on' at row 1

Question

I am importing a flat file into a table in DBeaver v7.0.2. When I attempt to import, it's throwing an error for date field formatted as mm/dd/yy.

The date column in the table is defined with datatype DATE.

I receive the following error:

Data truncation: Incorrect datetime value: '7/21/15' for column 'announced_on' at row 1

Any ideas on why it's complaining about the date format? It appears that I do not need to explicitly specify date format in the CREATE TABLE statement.

Answer 1

the normal date format is 2020-04-19.

If you try to insert 7/21/15 into a date field this will fail as you noticed.

Most imports allow to reformat input.

In mysql you would use STR_TO_DATE('7/21/15', '%m/%e/%y') to import data.

If you don't want to rewrite or need the dates that way , you could define the field as varchar(10) that is big enough to hold it.

Answer 2

check this:

SELECT STR_TO_DATE('7/21/15', '%c/%e/%y') AS str_to_date
      ,CAST('7/21/15' AS date) as implicit_converstion

implicit conversion doesn't recognize your string as date