[SOLVED] What's the best way to concat my multiple columns into datetime, when I have mixed data in columns, like both varchar and Integer?

Issue

So data is a table with the date and time info split into 4 columns, some varchar, others integer:

Year   Month   Day   Time
2022   May     20    18:43
1982   Feb     01    00:23
1942   Jan     13    16:17

Month and time are varchar, and year and day are integer.

I have to query via MYSQL to find certain dates, and it would be easier if the field I search was datetime like ‘2022-05-20 18:43’

I have searched all day yesterday and find a lot of examples that come close to what I need but not fit exactly, and I’m not good enough with MYSQL to figure this out.

I assume I have to concat() at some point, but then there is the CONVERSIONS I have to make on the varchar’s

I want to run a query that creates a NEW COLUMN in the DB which is datetime and then I can just query straightforward. So create a new column called ‘date2’ which is datetime, which I already created, just have to concat somehow and then move each rows data to it. Like this:

Year   Month   Day   Time    Date2
2022   May     20    18:43   2022-05-20 18:43
1982   Feb     01    00:23   1982-02-01 00:23
1942   Jan     13    16:17   1942-01013 16:17

Here is the Table Schema:

CREATE TABLE `lunar2` (
  `year` int(4) unsigned NOT NULL,
  `month` varchar(3) NOT NULL,
  `day` int(2) NOT NULL,
  `time` time NOT NULL,
  `sign` varchar(15) NOT NULL,
  `other` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Here is the code that worked. I thank the person who provided the code:

UPDATE table1 SET
other =  STR_TO_DATE(CONCAT_WS(' ', Year, Month, Day, Time), '%Y %M %d %H:%i:%s');

Solution

Try using STR_TO_DATE() to create a DATETIME value.

ALTER TABLE yourTable ADD COLUMN Date2 DATETIME;

UPDATE yourTable SET
    Date2 = STR_TO_DATE(CONCAT_WS(' ', Year, Month, Day, Time), '%Y %b %d %H:%i:%s');

UPDATE: Since your Time field is actually of type TIME, then we need to use %H:%i:%s to correctly parse it.

Answered By – Rocket Hazmat

Answer Checked By – Dawn Plyler (BugsFixing Volunteer)