MySQL Alter Table using existing field values

Question

Hello,

I am working with a database that has a table called date, which contains a separate field for day, month, year. Clearly this is not ideal when I am trying to run comparisons, etc. I am wondering is it possible for me to add a DateTime field to each row of this table and insert a concatenated string into the new field from the existing day, month, year fields.

I am quite sure its possible, I'm just wondering if anyone might be able to point me in the right direction on how to achieve this?

Below is the current date table: (i know)

CREATE TABLE IF NOT EXISTS `date` (
  `deposition_id` varchar(11) NOT NULL default '',
  `day` int(2) default NULL,
  `month` int(2) default NULL,
  `year` int(4) default NULL,
  PRIMARY KEY  (`deposition_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 1

What is the problem, I don't understand? Alter the table, add new DATE column and then populate it with a string "yyyy-mm-dd" using CONCAT mysql function or whatever.

Answer 2

first use alter table query:

alter table date add column datetimefield datetime NOT Null

then

use update query with self join on date and update datetimefield with concat on date,month, year column values.

Answer 3

Try this (untested) -

UPDATE date d SET d.datetime = (SELECT CONCAT('-','year','month','day') from date d1 where d1.id = d.id);