CODEWITHSUNDEEP
sqlmysqldatetime
Germstorm
Germstorm

Reputation: 9849

MySQL query to copy date from different columns

I have the following 6 integer db columns:

I need a query that takes these values for every row, combines them into a DATETIME and puts into another column.

Upvotes: 1

Views: 298

Answers (4)

Anatoliy
Anatoliy

Reputation: 30103

CREATE TABLE `x` (
  `y` int(4) DEFAULT NULL,
  `m` int(4) DEFAULT NULL,
  `d` int(4) DEFAULT NULL,
  `h` int(4) DEFAULT NULL,
  `i` int(4) DEFAULT NULL,
  `s` int(4) DEFAULT NULL
);
INSERT INTO x SELECT 2009, 9, 1, 9, 59, 59;
SELECT STR_TO_DATE(CONCAT(y,'-', m,'-', d,'-', h,'-', i,'-', s), '%Y-%m-%d-%h-%i-%s') FROM x;

Upvotes: 0

Eric
Eric

Reputation: 95153

You can put them all into a string and then convert that string to datetime, like so:

update tbl
set datecol = cast(cast(`year` as varchar) + '-' +
              cast(`month` as varchar) + '-' + 
              cast(`day` as varchar) + ' ' +
              cast(`hour` as varchar) + ':' +
              cast(`minute` as varchar) + ':' +
              cast(`second` as varchar) as datetime)

Although, I think MySQL uses concat, so it would be:

cast(concat(cast(`year` as varchar), '-', cast(`month` as varchar), '-',...) as datetime)

Upvotes: 1

Paul Dixon
Paul Dixon

Reputation: 300975

You can use CONCAT your data together to make something that looks like a DATETIME value

CONCAT(year,'-', month, '-', day,' ',hour, ':', min, ':', sec)

While you could use that directly, you could also turn into a real DATETIME by using STR_TO_DATE with it, e.g.

STR_TO_DATE(
   CONCAT(year,'-', month, '-', day,' ',hour, ':', min, ':', sec),
   '%Y-%m-%d %H:%i:%S');

Upvotes: 0

Greg
Greg

Reputation: 321766

It might not be the most efficient but you could do this:

UPDATE my_table
SET my_date = CONCAT(my_year, '-', my_month, '-', my_day, ' ',
    my_hour, ':', my_mins, ':', my_seconds)
WHERE ...

Upvotes: 1

Related Questions