0

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.

Improve this question

2 Answers 2

Reset to default
0

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.

Improve this answer
2
  • 2
    @kms - Is that 1915 or 2015? Or 15 AD? One should be aware of how (nondeterministic) the "2 to 4 year conversion" happens The DB might even think it is 2115. In short, never use 2 digit years.
    – Michael Kutz
    Commented Apr 19, 2020 at 18:07
  • this would be 2015 and we must wait till they tuc gets 64 bit finally, stiring data in a non mysql format is a bad design, because you can format every date in a format you like but for using date fuicntions you need a mysql date, so you have to convert it everytime.
    – nbk
    Commented Apr 19, 2020 at 18:33
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.