Update mysql table column

Question

In mysql database I've contact_details table. In this table a column name created_date2 contain following date values :

Formate : m / d y 
-----------------
10/10/2014 
10/10/2014 
6/19/2013 
6/19/2013
6/19/2013
6/19/2013
6/19/2013
6/19/2013
6/19/2013
6/19/2013
10/10/2014
10/10/2014 
10/10/2014 
3/10/2014

I want to update this column value which Month (m) has not Zero (0) to 06, 07 08 etc. For example, As you can see there are a few date 6/19/2013. I wan tto update this to 06/19/2013 using sql query. Can someone tell how can I do this ?

Note : I can do this by manually but there are more than 2500 rows exist !!

which data type is used for date if it is data format then you can save y/m/d format — Rashid, Nov 22 '14 at 05:19

score 0 · Accepted Answer · answered Nov 22 '14 at 05:13

0

If you are sure, this is for the first part i.e. the month part problem only, then try this:

UPDATE contact_details 
SET created_date2 = CONCAT('0', created_date2) 
WHERE LENGTH(created_date2) < 10

answered Nov 22 '14 at 05:13

Riad

3,822
5
28
39

Thanks for your reply But it's not updating. – Babu Nov 22 '14 at 05:18
Currently created_date2 column has char(20) data type. Can I change it to timestamp data type ? If so then will it be 0000000000 ? (I want to change the current date value to timestamp format) – Babu Nov 22 '14 at 06:06
http://stackoverflow.com/questions/18842892/what-happens-when-changing-column-type-timestamp-to-datetime-in-mysql – Riad Nov 22 '14 at 06:36
1

and to change the existing data to timestamp you need to update it first: `UPDATE contact_details SET created_date2=FROM_UNIXTIME(created_date2) ` – Riad Nov 22 '14 at 06:37

score 0 · Answer 2 · answered Nov 22 '14 at 05:21

0

UPDATE mytable 
   SET col = IFNULL(DATE_FORMAT(STR_TO_DATE( col ,'%m/%d/%Y'),'%m/%d/%Y'), col )

answered Nov 22 '14 at 05:21

spencer7593

106,611
15
112
140

Currently `created_date2` column has char(20) data type. Can I change it to `timestamp` data type ? If so then will it be `0000000000` ? (I want to change the current `date value` to `timestamp` format) – Babu Nov 22 '14 at 06:06

Update mysql table column

2 Answers2