We help IT Professionals succeed at work.

SQL Need to calculate age from DOB, no stored procedures or functions allowed

816 Views
Last Modified: 2017-03-31
I need to calculate exact age from today.  It must be in T-SQL I can not create a store procedure or function.  Another twist is the date as stored in a Varchar(255) type.   It has a format like  YYYY-mm-dd  ex 1971-02-12.  The database is SQL 2005.

Thank You
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Good luck with that.   Your requirements are to consume a date value stored in a varchar(255), validate that it's a good date, then calculate age to include year/month/day differences, all of which require some kind of script such as a stored procedure or function.   You can store an expression in a view but that's asking a lot.

Who's requirement is it to not allow a SP or function?

So for the moment I'll go with 'you can't do that' as the correct answer.

>The database is SQL 2005.
Just to 'check the box', this version is five version sold (2008, R2, 2014, 2014, 2016) and is no longer supported by Microsoft, so your client needs to 'live in the now' and upgrade.
Charles BaldoSoftware Developer

Author

Commented:
Thanks Jim

I do know its a good date.  Just year from this point is good.  Sorry that I implied month and day.   I do not have create rights for SP or Functions.  It is FDA validated database and if that changes it needs to be re validated. That's not going to happen. Cant create a view either.
Charles BaldoSoftware Developer

Author

Commented:
Jim By the way this is part of an upgrade in an attempt to live in the now.  Client is massive with almost a billion records in the database
Charles BaldoSoftware Developer

Author

Commented:
I have requested deleting this because I did not explain why and what to the best. I reentered it hopefully more descriptive of issue
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
I object to the deletion as my answer was correct as the question was asked, and should not be penalized for the question not being accurate in the first place.

Check out ​Top 10 Ways to Ask Better Questions for more helpful tips on how to engineer questions to get faster answers.
Charles BaldoSoftware Developer

Author

Commented:
I actually disagree with you that the answer "you can't do that"  is correct even in the moment.  I would have never awarded any points or accepted that as an answer. Your sarcasm as well was not appreciated either. I am not going to tell my good, billion dollar company client to "Live in the now" or sarcastically wish them luck and inform them that 2005 is obsolete,  they know that. That is why we are doing this.

If the points are the only thing driving you and you want them and the admins are okay with an answer in the moment. Then you can have them.
EE Topic Advisor, IT Consultant
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
You can do it easily by using the CONVERT function. Example:
DECLARE @MyDate AS VARCHAR(255) = '1971-02-12'

SELECT DATEDIFF(year,CONVERT(smalldatetime,@MyDate,104),GETDATE())

-----------
46

(1 row(s) affected)

Open in new window

You just need to replace @MyDate variable with your current column name.
Charles BaldoSoftware Developer

Author

Commented:
Thank You,

This can work.  I went back and forth on whether needed exact age.  Discussed with manager and we do. It is holding Medical records that by law need a cutoff if we are not going to migrate records. All records on Minors at time of document must be migrated  to a new system. So if a Minor was 17 year and 8 months we must do it.  It is a hospital records system. Old one is very very very large that has collected records for 25 years and all kinds of HIPPA laws on what I do with data apply.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

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