Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do I convert dd/mm/yy to YYYY-MM-DD
Database (MySQL)
DISCLAIMER:
Aways backup your data prior to running any code listed below. Rule number one applies. You break it , YOU have to fix it. Caution is a good thing, and I'm miles away
---------------------------------------------------------------------------
You have a date field in your existing db and you wish to use the dates from it in MySQL in a field of type DATE. You will need to reformat the dates.
1) Ensure you have 2 date fields in your new table; one of type DATE (new_date) and an additional field VARCHAR(10) (old_date) into which you insert your original records.
2) Run the following update on the table:
UPDATE my_table SET new_date=date_format(concat(@year:=substring(old_date ,7,2),@month:=substring(old_date ,4,2),@day:=substring(old_date ,1,2)), '%Y-%m-%d');
*note:
This is for dd/mm/yy but you can move the elements around in the UPDATE query.
This query will run on just one field if required, just set the source and target field names the same (make sure the type of field is correct first).
Aways backup your data prior to running any code listed below. Rule number one applies. You break it , YOU have to fix it. Caution is a good thing, and I'm miles away
---------------------------------------------------------------------------
You have a date field in your existing db and you wish to use the dates from it in MySQL in a field of type DATE. You will need to reformat the dates.
1) Ensure you have 2 date fields in your new table; one of type DATE (new_date) and an additional field VARCHAR(10) (old_date) into which you insert your original records.
2) Run the following update on the table:
UPDATE my_table SET new_date=date_format(concat(@year:=substring(old_date ,7,2),@month:=substring(old_date ,4,2),@day:=substring(old_date ,1,2)), '%Y-%m-%d');
*note:
This is for dd/mm/yy but you can move the elements around in the UPDATE query.
This query will run on just one field if required, just set the source and target field names the same (make sure the type of field is correct first).