Convert YYYY-mm-dd to yyyymmdd in MYSQL

Question

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.

is it a string or a date time type? – Daniel A. White Aug 06 '16 at 12:18 — Daniel A. White, Aug 06 '16 at 12:18
You can do this kind of thing in your css or javascript – Strawberry Aug 06 '16 at 12:25 — Strawberry, Aug 06 '16 at 12:25

score 2 · Answer 1 · answered Aug 06 '16 at 12:23

2

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

answered Aug 06 '16 at 12:23

Nayan Sharma

Very useful technique. Could you link to a reference? – Ricky McMaster Oct 25 '18 at 11:04

score 0 · Answer 2 · answered Aug 06 '16 at 12:20

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.

score 0 · Answer 3 · edited May 23 '17 at 12:00

0

You can use this

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

edited May 23 '17 at 12:00

Community

answered Aug 06 '16 at 13:50

Shafi

score 0 · Accepted Answer · answered Aug 06 '16 at 16:51

0

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'

answered Aug 06 '16 at 16:51

Kyon147

4 Answers4