I have spent a couple hours searching for this online. I also searched here and got 246 results. I apologize if I am asking a duplicate question here but my brain is mashed potatoes at this point.
In my Consumer table I have a datetime column where we store a person's birth date. Simple. I need a column that will store their age based on that date. From what I have found so far this seems overtly complicated.
I created a column and under the Computed Column Specification I dropped in the formula (datediff(year,[Date of Birth],getdate()).
I know that naming a column "[Date of Birth]" is a bad practice. The sloppy naming of columns will be corrected in my next big revision of the entire database.
That being said, (datediff(year,[Date of Birth],getdate()) gives me an accurate age down to the year only. A person who was born in November of 1956 is showing to be 59 years old even though their birthday is still three months away.
(datediff(dayofyear,[Date of Birth],getdate()) gives me the number of days they have been alive and (datediff(dayofyear,[Date of Birth],getdate())/(365)) gives me the number of years they have been alive and then expresses the months as a decimal (58.71321 years old).
I know what you think I am about to ask.......
Is there a way to modify the scale of this column so that it removes everything to the right of the decimal point? It will not let me change it in Access. If I can hide the decimals my coworkers will just see the (mostly) correct age of the consumer on the form.
I am not above just painting a box over the text field on the form to hide the decimals if I have to.