CODEWITHSUNDEEP
mysql
WebDevB
WebDevB

Reputation: 492

Converting A SQL Column From VARCHAR to Date

After my last post: SQL Query On Date When Type Is VARCHAR

I've decided I'm going to convert the column from a VARCHAR to a DATE type.

All the fields already have the data setout like "22/01/14".

Has anyone done this before.

Thanks,

Scott

Upvotes: 0

Views: 88

Answers (2)

Alex Kapustin
Alex Kapustin

Reputation: 2449

I suggest you change table structure in several sql like this:

alter table `mytable` add `_IssueDate` DATE NOT NULL after `IssueDate`;
update `mytable` set `_IssueDate`=STR_TO_DATE(`IssueDate`, '%d/%m/%Y');
alter table `mytable` drop `IssueDate`;
alter table `mytable` change `_IssueDate` `IssueDate` DATE NOT NULL;

Upvotes: 0

Alma Do
Alma Do

Reputation: 37365

Use STR_TO_DATE() like:

mysql> SELECT STR_TO_DATE('22/01/14','%d/%m/%Y');
+------------------------------------+
| STR_TO_DATE('22/01/14','%d/%m/%Y') |
+------------------------------------+
| 2014-01-22                         |
+------------------------------------+
1 row in set (0.00 sec)

-so apply it in your UPDATE statement. If you're going to change column type, then it will not be safe to change that directly. Let's say your current column has name str_date. Then:

  • Add new column, like: ALTER TABLE t ADD new_date DATE
  • Copy proper values: UPDATE t SET new_date=STR_TO_DATE(str_date,'%d/%m/%Y')
  • Drop old column: ALTER TABLE t DROP str_date
  • Rename new column: ALTER TABLE t CHANGE new_date str_date DATE

Upvotes: 1

Related Questions