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;
first use alter table query:
alter table date add column datetimefield datetime NOT Null
use update query with self join on date and update datetimefield with concat on date,month, year column values.
Try this (untested) –
UPDATE date d SET d.datetime = (SELECT CONCAT('-','year','month','day') from date d1 where d1.id = d.id);
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.