SQL command to update the age field based on date of birth

• jaylee

• November 22, 2023 at 5:35 am

Hi good day,

Hi I add 2 additional fields for users,

mag_date_of_birth
mag_age

Now i try to update the age of all user based on the date of birth thru sql command and here is my command,

UPDATE wp_usermeta AS m1
JOIN wp_usermeta AS m2 ON m1.user_id = m2.user_id
SET m1.meta_value = DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), m2.meta_value)), '%Y') + 0
WHERE m1.meta_key = 'mag_age'
  AND m2.meta_key = 'mag_date_of_birth'

now my problem here is when I execute that command it returns and error said “Incorrect datetime value”

mag_date_of_birth field is datepicker

where it saves data on database like this “19911207”

I think 19911207 and 1991-12-07 is different…

can anyone correct my sql command please…

screenshots
https://imgur.com/a/Gv2uxVx

sql.png.png

• John Huebner

• November 22, 2023 at 8:50 am

This must be done in php and cannot be done with an SQL query. ACF stores dates in “Ymd” format and not as a valid SQL date value. “Y-m-d H:i:s”.

If you are doing this with a cron you will need to get all of the users, loop over them, get the fields and calculate in PHP and then update the field.

• jaylee

• November 24, 2023 at 2:52 am

but it says here that its possible thru sql command…

https://www.scaler.com/topics/how-to-calculate-age-from-date-of-birth-in-sql/

I also posted this problem on stackoverflow…

https://stackoverflow.com/questions/77521929/sql-command-query-to-update-date-of-birth-to-age-not-valid

• jaylee

• November 24, 2023 at 3:36 am

May I answer my question..this solve my problem and it’s possible to achieved this through sql command query like the one below…I execute this 2 command on phpmyadmin

or thru plugin used this – SQL Executioner, not updated for a long time but still working till now..I don’t have any problem with..

UPDATE wp5m_usermeta AS m1
JOIN wp5m_usermeta AS m2 ON m1.user_id = m2.user_id
SET m1.meta_value = DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), m2.meta_value)), '%Y') + 0
WHERE m1.meta_key = 'mag_age'
  AND m2.meta_key = 'mag_date_of_birth'

or this one also works

UPDATE wp5m_usermeta AS m1
JOIN wp5m_usermeta AS m2 ON m1.user_id = m2.user_id
SET m1.meta_value = TIMESTAMPDIFF(YEAR, m2.meta_value, CURRENT_DATE)
WHERE m1.meta_key = 'mag_age'
  AND m2.meta_key = 'mag_date_of_birth'

You can use STR_TO_DATE() to parse dates in other formats. STR_TO_DATE(m2.meta_value, ‘%Y%m%d’

Thank you sir John Huebner

• jaylee

• November 24, 2023 at 10:31 am

re: but it says here that its possible thru sql command…

https://www.scaler.com/topics/how-to-calculate-age-from-date-of-birth-in-sql/

I also posted this problem on stackoverflow…

https://stackoverflow.com/questions/77521929/sql-command-query-to-update-date-of-birth-to-age-not-valid