Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

ie

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

3 Answers 3

up vote 3 down vote accepted

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 make sure it's what you're really looking for.

share|improve this answer
    
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! –  daniel Crabbe Nov 27 '12 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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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