How to change VARCHAR type to DATETIME using ALTER in MySQL?

Question

How can I change VARCHAR() type to DATETIME using ALTER in MySQL?

Do you have existing data in your varchar column that you want to convert into datetime? — beny23, Mar 11 '13 at 10:16
@beny23, Pranav: what should I do If there is some data exist in table. — ABC, Apr 27 '14 at 05:27
@beny23 Yes. It's an ISO8601 formatted date/time string. Will MySQL convert it correctly? — Michael, Dec 21 '17 at 22:21

score 25 · Answer 1 · answered Mar 11 '13 at 10:13

25

ALTER TABLE <tblName> MODIFY <columnName> dataType constraint;

For your requirement it will be

ALTER TABLE <tblName> MODIFY <columnName> datetime;

answered Mar 11 '13 at 10:13

Meherzad

Dhinakar · Answer 2 · 2013-03-11T11:12:23.643

2

Try this query.

ALTER TABLE  `table_name` CHANGE  `From Date`  `From Date` DATETIME NULL DEFAULT '0000-00-00 00:00:00';

edited Mar 11 '13 at 11:12

answered Mar 11 '13 at 10:15

Dhinakar

1

Zero dates are not valid in mysql 5.7+. Default to either `NULL` or `"1970-01-01 00:00:01"` (unix epoch, first valid date in strict mode). Other options are `DEFAULT NOW()` or `ON UPDATE CURRENT_TIMESTAMP` depending on need. Note that `DEFAULT NOW()` is not legacy compatible. – mopsyd Nov 12 '18 at 16:54

score -1 · Answer 3 · answered Apr 04 '13 at 07:09

-1

Why not you just use

STR_TO_DATE(str,format) ,

It takes a string str and a format string format and returns a DATETIME value if the format string contains both date and time parts.

Reffer this LINK , Hope it may help you

answered Apr 04 '13 at 07:09

3 Answers3