mysql adjusting datetime values in the dbase, not in search returns

Question

This must be a ridiculously stupid question, because after numerous searches of the StackOverflow question archives (and elsewhere) I can't find an answer.

I recently posted a question about managing date & time data here:

http://stackoverflow.com/questions/1587116/mysql-date-storage-query-performance-with-php

Following Mike B's advice I appended two zero's onto the end of my YYMMDDHHMM strings to make them DATETIME format compliant as YYMMDDHHMMSS and then copied them to a new column with DATETIME formatting. To my surprise (although perhaps predictably), when the script finished I now had a DATETIME column where every year was of the 20XX variety, rather than 19XX!

Oops.

Since then I've been trying to figure out how to change it via acting directly on the DATETIME column rather than trashing the new column and reusing the original integers (I want to do it the "right" way) and am guessing I'm supposed to use DATE_SUB and INTERVAL 100 YEAR but am stumped as to the actual construction of the code either directly in MySQL or via PHP.

Again, I apologize, this must be really freaking obvious.

Thank you,

• A

Answer 1

update my_table
   set my_date_column = date_sub(my_date_column, interval year 100);