We help IT Professionals succeed at work.

Calculate Age in SQL Server Express

118 Views
Last Modified: 2018-10-05
Hi,

In SQL Server Express 2017, I want to calculate Age in a table containing 100 records.
Let us say different DOBs are saved some 25 years before today. In the new column Age, this result is desired 25y 5m 3d.

-San
Comment
Watch Question

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
Analyst Assistant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Sanjay GandhiFounder, Kenhal

Author

Commented:
Both answers can be tweaked to arrive at a combined result.
However, it requires a little long code to find the answer.

This is a result in years and months.
Update statement is applied to update AccountAge checking AccountOpenDate compared with current date.

Update Customers
Set AccountAge = Str(DateDiff(YEAR,AccountOpenDate,GetDate())-IIF(Month(AccountOpenDate) <= Month(GetDate()),0,1),2,0) + 'y ' + Str(IIF(Month(AccountOpenDate) <= Month(GetDate()), Month(GetDate())-Month(AccountOpenDate), 12-Month(AccountOpenDate)+Month(GetDate())),2,0) + 'm'

Thanks.
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
I assumed you wanted the age in years only.

By the way, why are you storing the age in a table?
Sanjay GandhiFounder, Kenhal

Author

Commented:
On a monthly basis, the Account age is to be calculated, so that based on age of the account, the processes can be carried out. For example, mapping some plans and sharing them with the Customers based on the age of the their account. So it will be easy to work on saved age of the Account.
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
If that's the case you shouldn't be updating the age every month in the table, you should use a query that returns the age and use that.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
Why not create a view that contains the age calculation?
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.