What's the best way to concat my multiple columns into datetime, when I have mixed data in colu-CodePudding

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');

CodePudding user response：

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');

CodePudding user response：

Here is an example of what you are trying to do.
When converting string to date it is important to get the format string right, otherwise we get a null value.

create table datess(
y int,
m varchar(3),   
d int,
t varchar(5));
insert into datess (y,m,d,t)values
(2022 ,'May',  20    ,'18:43'),
(1982 ,'Feb',  01    ,'00:23'),
(1942 ,'Jan',  13    ,'16:17');

SELECT 
  STR_TO_DATE(
  CONCAT(y,'-',m,'-',d,' ',t),
  '%Y-%M-%d %H:%i') date_format
FROM datess;

| date_format         |
| :------------------ |
| 2022-05-20 18:43:00 |
| 1982-02-01 00:23:00 |
| 1942-01-13 16:17:00 |

db<>fiddle here