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

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.

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.

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

I have requested deleting this because I did not explain why and what to the best. I reentered it hopefully more descriptive of issue

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.

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.

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)

Select allOpen in new window

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

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.