Solved How to update the century in a date field? (1 Viewer)

rosef

New member
Local time
Today, 00:51
Joined
Jan 18, 2021
Messages
9
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

I’m here to help
Staff member
Local time
Today, 00:51
Joined
Oct 29, 2018
Messages
21,358
How about subtracting 100 years from it? Just a thought...
 

Isaac

Lifelong Learner
Local time
Today, 00:51
Joined
Mar 14, 2017
Messages
8,738
1611082617227.png


Edit I was a moment too slow.

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

Minty

AWF VIP
Local time
Today, 07:51
Joined
Jul 26, 2013
Messages
10,355
You could use DateAdd("yyyy",-100,[YourDateField])


Edit - too slow....
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:51
Joined
Feb 19, 2002
Messages
42,981
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.
 
Last edited:

Users who are viewing this thread

Top Bottom