Calculate Age in SQL Server Express

Sanjay Gandhi
Sanjay Gandhi used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Analyst Assistant
Commented:
Try this.

DATEDIFF(YY,DOBField, GetDate()) -
       CASE WHEN DATEADD(YY,DATEDIFF(YY,DOBField, GetDate()),DOBField)> GetDate() THEN 1
            ELSE 0
       END
AS [Age]
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

NorieAnalyst Assistant

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

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
Most Valuable Expert 2014
Awarded 2013

Commented:
Why not create a view that contains the age calculation?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial