field is meta_value and current value is like 01/01/2006

i want to change it to a string 20060101 so thought i could get the value and update it with the characters in the correct order.


UPDATE wp_postmeta SET meta_value = meta_value[9]meta_value[8]meta_value[7]meta_value[6] 
  • 1
    Please state RDBMS. String manipulation functions are not well standardised. Nov 27, 2012 at 12:54
  • 1
    Also tell us if you have 20th of Jan stored as 20/01/2006 or 01/20/2006. Nov 27, 2012 at 12:59
  • Hello - 'current value is like 01/01/2006' & what is RDBMS?!
    – v3nt
    Nov 27, 2012 at 13:08
  • @danielCrabbe - He is asking which database are you using, Mysql, Postgresql, etc. Nov 27, 2012 at 13:10
  • 1
    Wordpress means (99%) mysql. Nov 27, 2012 at 13:26

3 Answers 3


Assuming that meta_value is always dd/mm/yyyy and you are looking for yyyymmdd (which isn't clear), try this:

UPDATE wp_postmeta 
SET meta_value = CONCAT(SUBSTRING(meta_value FROM 7), 
     SUBSTRING(meta_value, 4, 2), 
     SUBSTRING(meta_value, 1, 2));

Run that as a SELECT first to verifying the output before update and make sure it's what you're really looking for.

  • ok - ran this Select CONCAT(SUBSTRING(meta_value FROM 7), SUBSTRING(meta_value, 4, 2), SUBSTRING(meta_value, 1, 2)) FROM wp_postmeta WHERE meta_key = 'end_date' and all looks great. That is going to come in v handy. Thanks!
    – v3nt
    Nov 27, 2012 at 14:17

As the field is stored as varchar(255) in that table (that is, it's stored as a string, rather than a special date format), you'll need to slice it and then join the chunks together.

Assuming that the original date is dd/mm/YYYY and the required format is YYYYmmdd then you could do something like this:

UPDATE wp_postmeta SET meta_value = concat(substring(meta_value,7,4),substring(meta_value,4,2),substring(meta_value,1,2))

This is using the substring function to split the original string, the format of this command is:

substring(string to split,starting position in string, length of required substring)

This isn't the only format, more examples are here - http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr

Then we use the concat function to join the 3 new strings as one (details on that one here - http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat)

But you'll need to be very careful running that update as it is. At the moment it will do that for every row in the table. So if you have a row with post_meta='Stuart Moore' it will be updated to 'MooarSt' which probably isn't what you want. So make sure you use a where clause to restrict which rows you're modifying.


If meta_value field type is a DATE then you could use

UPDATE wp_postmeta 
SET meta_value = DATE_FORMAT(meta_value, '%Y%m%d');

or if it is a string then use

UPDATE wp_postmeta 
SET meta_value = DATE_FORMAT(STR_TO_DATE(meta_value, '%d/%m/%Y'), '%Y%m%d');

More on MySQL date functions

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.