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');
STR_TO_DATE() to create a
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)