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

Posted on 2012-04-10
Last Modified: 2012-04-11
Our database contains a table called accountinfo with a column called fields_4 the current format is this:

This is what we would like to have.
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) .
Question by:CityInfoSys
LVL 24

Accepted Solution

johanntagle earned 500 total points
ID: 37830814
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


Author Closing Comment

ID: 37832846
Thanks for taking the time to show the example.

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now