Solved How to update the century in a date field?

[rosef]

I am doing some data clean-up on a birthdate field. I have over 5000 records that need the 20XX changed to 19XX of the date field. Is there away that I can update just the 20 to 19? If so, how?

 

[theDBguy]

How about subtracting 100 years from it? Just a thought...

 

[Isaac]

Edit I was a moment too slow.

Yep - either what dbGuy said, or subtract 1 year from it.

 

[Minty]

You could use DateAdd("yyyy",-100,[YourDateField])


Edit - too slow....

 

[Pat Hartman]

Select the dates to change like this:

Where Year(DOB) > Year(Date())

Then subtract 100.

Do some testing first to make sure that the error is consistent. You may have some outliers that need to be changed manually. And don't forget to backup the database before you ever run an untested update query!!!!!

We also don't know what kind of data you are cleaning up. If it is employee data, then you want to change the Where to include a test to find people less than 18 or 16 if you hire children. You probably also want to include just a straight test for DOB > Date() since those would also be wrong.

And don't forget to fix the data entry form to prevent the problem in the future.