Convert Mysql varchar (10) dd/mm/yyyy to varchar (10) YYYY-MM-DD format

Our database contains a table called accountinfo with a column called fields_4 the current format is this:
3/15/2012      
3/16/2012      
3/17/2012      
3/18/2012      

This is what we would like to have.
2012-03-15      
2012-03-16      
2012-03-17      
2012-03-18      
We need to keep the format in varchar for scripting reasons not date format.

I was thinking something like this
Set Date_Field = Convert(datetime, VarChar_Field, 101) .
Thanks
LVL 2
CityInfoSysAsked:
Who is Participating?
 
johanntagleConnect With a Mentor Commented:
set Date_Field = str_to_date(VarChar_Field,'%m/%d/%Y')

While the output for str_to_date is of date datatype, it can be stored to a varchar field.  See below:

mysql> create table test_date(v varchar(10), d varchar(10));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into test_date (v) values ('3/15/2012'),('3/16/2012'), ('3/17/2012'), ('3/18/2012');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> update test_date set d=str_to_date(v,'%m/%d/%Y');
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from test_date;
+-----------+------------+
| v         | d          |
+-----------+------------+
| 3/15/2012 | 2012-03-15 |
| 3/16/2012 | 2012-03-16 |
| 3/17/2012 | 2012-03-17 |
| 3/18/2012 | 2012-03-18 |
+-----------+------------+
4 rows in set (0.00 sec)

Open in new window

0
 
CityInfoSysAuthor Commented:
Thanks for taking the time to show the example.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.