MySQL Replace functionality

Question

Hi I have a database that lists expiration dates etc...

5-22-2012
5-22-2012
5-22-2012
5-22-2012

but some came out like

5-22-1912
5-22-1912
5-22-1912

is there a way to REPLACE just those years at the end of my columns?

its DATE - I had to repair our database somehow a 9th of our patients turned up with these invalid expiration dates — Jeff Voss
– Jeff Voss, Commented Jul 24, 2011 at 7:18
As to why and how these values got into the table in the first place, check the insert procedure (form, webpage, whatever). Probably it doesn't have to do with MySQL as default MySQL behaviour is "# Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using the following rules: * Year values in the range 00-69 are converted to 2000-2069. * Year values in the range 70-99 are converted to 1970-1999. ". — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jul 24, 2011 at 7:28

ypercubeᵀᴹ · Accepted Answer · 2011-07-24 07:13:00Z

4

If field is DATE type:

--- Add 100 years when date is before 1940

UPDATE TableX
SET expirationDate = expirationDate + INTERVAL 100 YEAR
WHERE expirationDate < '1940-01-01'

answered Jul 24, 2011 at 7:13

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Edgar Velasquez Lim · Accepted Answer · 2011-07-24 07:11:14Z

1

UPDATE table 
SET thiscolumn = CONCAT(month(thiscolumn),'-', day(thiscolumn),'-2012')
WHERE year(thiscolumn) = '1912';

answered Jul 24, 2011 at 7:11

2,44418 silver badges15 bronze badges