CODEWITHSUNDEEP
mysqlwordpressdate
Kyon147
Kyon147

Reputation: 798

Convert YYYY-mm-dd to yyyymmdd in MYSQL

After trying Google and some other posts on here, I can not seem to find the answer.

I currently have dates stored in MYSQL as YYYY-mm-dd but for the ACF Date Picker in Wordpress it reads dates as yyyymmdd.

How can I convert the current format to the new format? All the other conversions seem to want it to have dashes but I was to take them away.

Upvotes: 2

Views: 12728

Answers (4)

Kyon147
Kyon147

Reputation: 798

Using all your advice I found the correct query to run.

UPDATE `wp_postmeta` 
SET meta_value = replace(meta_value, '-', '') 
WHERE `meta_key` = 'release_date'

Upvotes: 0

Shafi
Shafi

Reputation: 91

You can use this

$originalDate = "2010-03-21";
$newDate = date("ymd", strtotime($originalDate));

Here is almost duplicate Convert date format yyyy-mm-dd => dd-mm-yyyy

Upvotes: 0

Nayan Sharma
Nayan Sharma

Reputation: 1853

mysql> `SELECT col from tableA`
        -> '2008-06-13'
mysql> SELECT col+ 0 from tableA ;
        -> 20080613

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Dates are stored in the database using an internal format. To convert them to a string, use the date_format() function:

select date_format(col, '%Y%m%d')

If the dates are stored as strings, then just use replace():

select replace(col, '-', '')

You can write views over the tables, if you don't want to do this every time you select from the tables.

Upvotes: 0

Related Questions