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.

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.

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. Apr 19 '20 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
    Apr 19 '20 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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