Convert VARCHAR to Date

Navigation
1

Good day everyone. I know in advance that this question is already posted on the site, however the solutions I have tried give me unsatisfactory results. My situation is as follows.

I have a table ( logs3 ) with several fields of which the Date ( Date ) is saved as VARCHAR, now, I have created another field ( Date2 ) with the data type DATE.

As data of possible interest:

  • The table has just over 300,000 records.

  • The current values in field Date have the American format 'M / D / YYYY'.

  • PHPMyAdmin 4.7.1 in XAMPP for Windows.

  • Aurora DB instance

I tried with the sentence: 

UPDATE 'logs3' SET 'Date2'=  str_to_date(  'Date', '%d/%m/%Y')

And it runs correctly, but only with the first 51163 fields. I have verified and before as well as after registration 51163 the date is handled in the same way, format and value, nothing changes before or after that registration.

Surely I'm missing something. I beg your help please.

Thanks in advance.

    
asked by Neoz Memphisto 22.02.2018 в 18:17
source

1 answer

1

Amid painful laughter and slaps to the forehead I have found the solution. Even I have specified it in the question, but very stupidly I have overlooked it.

The format that VARCHAR handles is in AMERICAN M / D / YYYY format. My sentence: 

UPDATE 'logs3' SET 'Date2'=  str_to_date(  'Date', '%d/%m/%Y')

It has the European format D / M / YYYY, therefore (I suppose) when arriving at a certain record in which the second pair of digits exceeds the value of 12 the sentence stops without returning error, because it succeeds in modifying the other 51163 records.

So by inverting the '% d /% m /% and' parameter to '% m /% d /% y' 300 thousand missing records are completed.

Thank you all for your time and healthy participation.

    
answered by 23.02.2018 / 23:49
source
How to integrate Go's famework with ReactJs? Date__range inclusive in Django