Update wordpress custom field date format

February 26, 20231 Views

In a serious oversight on my part, I have sorted all date formats as %M %d %Y (July 3 2012) rather than %Y %m %d (2012 07 03).

I want to convert all of the dates of a custom field (called ‘game_release_date’) to the new format.

The code I’ve got so far (but doesn’t work) is:

UPDATE `wp_postmeta` 
SET meta_key = DATE(STR_TO_DATE(game_release_date, '%m %d %Y'))
WHERE DATE(STR_TO_DATE(game_release_date, '%M %d %Y'));

Post Views: 1

1 comment

Anonymous says:

February 26, 2023 at 7:02 pm
To convert the string containing July 03 2012 into a MySQL date, use MySQL’s STR_TO_DATE() function:
```
STR_TO_DATE(game_release_date, '%M %d %Y')
```
I’d recommend storing the result of this function in a column of the DATE datatype.

To convert a MySQL date (whether the result of the above function, or a DATE column) into 2012 07 03, use MySQL’s DATE_FORMAT() function:
```
DATE_FORMAT(date_value, '%Y %m %d')
```
Therefore, if you want to keep your dates in a string (generally a bad idea) but in the new format:
```
UPDATE wp_postmeta
SET    game_release_date = DATE_FORMAT(
         STR_TO_DATE(game_release_date, '%M %d %Y'),
         '%Y %m %d'
       )
WHERE  meta_key = ...
```
Log in to Reply