CODEWITHSUNDEEP
mysqldatetime
Ricardoke
Ricardoke

Reputation: 153

How to convert text column to datetime MYSQL

I have a script that every five minutes updates the values of the table in MySql server. The problem is that in one column called 'Date' I have a date and time in text. like this:

Date

24/04/2021 08:22:01
24/04/2021 08:22:02
24/04/2021 08:22:03
...

I want to be able to convert that into a datetime so I can then manage the max or min date. I have seen a lot of topics talking about this, but they all are in SQL and not MySql.

Upvotes: 0

Views: 346

Answers (1)

Akina
Akina

Reputation: 42642

CREATE TABLE test (`Date` VARCHAR(255));
INSERT INTO test VALUES
('24/04/2021 08:22:01'),
('24/04/2021 08:22:02'),
('24/04/2021 08:22:03');
SELECT * FROM test;

| Date                |
| :------------------ |
| 24/04/2021 08:22:01 |
| 24/04/2021 08:22:02 |
| 24/04/2021 08:22:03 |

-- variant 1 - transform in a query
SELECT `date`, STR_TO_DATE(`date`, '%d\/%c\/%Y %H:%i:%s') formatted_date
FROM test;

date                | formatted_date     
:------------------ | :------------------
24/04/2021 08:22:01 | 2021-04-24 08:22:01
24/04/2021 08:22:02 | 2021-04-24 08:22:02
24/04/2021 08:22:03 | 2021-04-24 08:22:03

-- variant 2 - create generated column
ALTER TABLE test 
  ADD COLUMN formatted_date DATETIME AS (STR_TO_DATE(`date`, '%d\/%c\/%Y %H:%i:%s')) STORED;
SELECT * FROM test;

Date                | formatted_date     
:------------------ | :------------------
24/04/2021 08:22:01 | 2021-04-24 08:22:01
24/04/2021 08:22:02 | 2021-04-24 08:22:02
24/04/2021 08:22:03 | 2021-04-24 08:22:03

db<>fiddle here

Upvotes: 1

Related Questions