Issue
I have a table which pulls data from RSS Feeds and saves. The RSS feed format of representing publication date and time seems difficult to be converted into a MYSQL DATE(TIME) format. It comes as Thu, 14 Jan 2021 17:11:05 +0000 for example. So when I use STR_TO_DATE to try converting it, it returns null since STR_TO_DATE doesn’t go with such formats. Also, I can’t even trim parts of the string and make it work with STR_TO_DATE. Of course, other functions like UNIX_TIMESTAMP won’t work with that kind of string. How can I convert such a complex string to a MYSQL date format? I’m trying to do something like:
SELECT * FROM tbl WHERE 1 ORDER BY STR_TO_DATE('Thu, 14 Jan 2021 17:11:05 +0000', '%d-%m-%Y')
Solution
You need to complete your date format.
You can truncate the parts you don’t want afterwards.
As an alternative you could use substring before convertion.
select str_to_date( 'Thu, 14 Jan 2022 17:11:05 +0000', '%a, %d %b %Y %H:%i:%s +%f') formatted_date| formatted_date | | :------------------------- | | 2022-01-14 17:11:05.000000 |
db<>fiddle here
Answered By – Kendle
Answer Checked By – Senaida (BugsFixing Volunteer)