views:

39

answers:

3

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;
A: 

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.

FractalizeR
The problem is I do not know how to do it. Thats why I was asking.
TGuimond
Do what exactly? How to issue ALTER TABLE? Use CONCAT function? Go read some MySQL manual. Seriously. Why not? Or at least search google for alter table + concat mysql query examples. Go buy a good book on MySQL.
FractalizeR
+2  A: 

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.

seed_of_tree
You should add default value or not use `NOT NULL`.
M42
does't matter. As we will update it with concat on date,month, year
seed_of_tree
+1  A: 

Try this (untested) -

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