Mysql - Update column with Brazilian date value

Asked

Viewed 402 times

0

I am updating a table in a bank with more than 100 thousand records to be able to perform future SELECTS to generate a statistical report.

The table currently has a 'Birth' column of type varchar(10) which stores the customer’s date of birth in the format 'dd/mm/yyyy'. As in varchar is taking about 2 minutes to complete the query.

So I created a column called 'data_nascimento' of the date type and I’m trying to use the following code to convert the values of the 'Birth' column':

update cadastros_dados set data_nascimento = STR_TO_DATE(nascimento,'%d%m%Y')

But it makes the following mistake:

[Err] 1411 - Incorrect datetime value: '23/02/1971' for Function str_to_date

Why are you not accepting the date that is stored in 'Birth' in the STR_TO_DATE function? What am I doing wrong?

mysql database date

1 answer

1


  • I already put with the bars but still gives the following error:

  • [Err] 1411 - Incorrect datetime value: '20 01 1958' for Function str_to_date

  • 1

    I found the problem. Some records are dd/mm/yy and others are dd mm yyyy. Would there be some way I could update all these incorrect without having to be one by one?

  • 1

    gives a replace, where it has spaces /

  • @Renankaiclopes if helped you, do not forget to mark as reply. thanks

Browser other questions tagged mysql database date

You are not signed in. Login or sign up in order to post.