Avatar of Sanjay Gandhi
Sanjay Gandhi
Flag for India asked on

Calculate Age in SQL Server Express

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
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
PortletPaul

ASKER CERTIFIED SOLUTION
Norie

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sanjay Gandhi

ASKER
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.
Norie

I assumed you wanted the age in years only.

By the way, why are you storing the age in a table?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Sanjay Gandhi

ASKER
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.
Norie

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.
PortletPaul

Why not create a view that contains the age calculation?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.