Today I was working with a small MySQL data set. The data provided by a CSV file and needed to load it into AWS RDS MySQL. Since the RDS does not support the Load data inline. So I have manually convert the CSV file to .sql file. But the problem statement is, in CSV the date time column had two digit year format and while converting into sql it considered the day part as year and automatically added 20 in the beginning. MySQL has makedate function , but it didn’t work for me. My entire data for the year 2018.
The two digit format in CSV
Converted sql file
Create the table and load the test data
Convert two digit to four digit in MySQL:
Create a temporary table same as the original table but make the column datatype as datetime data type.
Select the original table and trim the first two characters from the yyyy.
Add 20 in the year (dd-mm- yy).
Do str_to_date to mention this as a datetime column.
Drop the old table and rename the temporary table.