Home > Software design >  What's the best way to concat my multiple columns into datetime, when I have mixed data in colu
What's the best way to concat my multiple columns into datetime, when I have mixed data in colu

Time:03-23

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

  • Related