CODEWITHSUNDEEP
mysqlsqldatabasesqldatatypes
Punkrock760
Punkrock760

Reputation: 51

MySQL - How do I change the format of a date which is using the mediumtext data type?

How do I change the format of a date which is using the mediumtext data type? I can't find a solution anywhere.

From:

+-------------+----------------------------+
|  Page Name  | Last Modified (mediumtext) |
+-------------+----------------------------+
| Page Name 1 | Jul 20, 2018 16:06         |
| Page Name 2 | Jun 04, 2019 11:16         |
| Page Name 3 | Sep 03, 2019 13:11         |
| Page Name 4 | Sep 19, 2019 14:00         |
| Page Name 5 | Dec 20, 2019 09:34         |
+-------------+----------------------------+

To:

+-------------+----------------------+
|  Page Name  | Last Modified (date) |
+-------------+----------------------+
| Page Name 1 | 2018-07-20           |
| Page Name 2 | 2019-06-04           |
| Page Name 3 | 2019-09-03           |
| Page Name 4 | 2019-09-19           |
| Page Name 5 | 2019-12-20           |
+-------------+----------------------+

Select Statement:

Select PageName, LastModDate from Content

I have tried using the below code but they return Null.

- CONVERT(LastModDat, DATETIME)
- DATE_FORMAT(LastModDat, "%Y-%m-%d")

Upvotes: 1

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use str_to_date() in MySQL:

select str_to_date(left(last_modified, 13), '%M %d, %Y')

This produces a date value. I do NOT recommend converting this to a string.

Upvotes: 1

Related Questions