Solved

# SQL Server calculate the exact age on the day

Posted on 2006-04-27
Medium Priority
905 Views
Hi, I have a date field in MS SQL Server with the name "mDOB" with the datatype "datetime" applied to it.

this dateofbirth is  in that field "05.09.1973"

I need to calculate the exact age and get it into flash.

This is what I do, it works but it misses on up to 10 days, and that is not acceptable.

Response.write "mAge=" & (DateDiff("yyyy", objRS("mDOB"), Now()))& "&"
0
Question by:oksum73
• 2
• 2

LVL 4

Expert Comment

ID: 16558749
Why missed 10 days? We just simply subtract this year to birth year, then we have the result !!! Can you explain in detail?
0

LVL 4

Accepted Solution

nguyenvinhtu earned 375 total points
ID: 16558788
Your result fail to pleasure you maybe because your Date and the Date today are not in the same format.
To make sure the two date is same format:
Response.write "mAge="
& (DateDiff("yyyy", DateFormat(objRS("mDOB"),"mm/dd/yyyy"), DateFormat(Now(),"mm/dd/yyyy"))) & "&"
0

LVL 44

Expert Comment

ID: 16559319
when you say 'te exact age', do you mean X years, Y months and Z days as the age , or do you simply want the age in Years?

DateDiff("yyyy".......) will simply return the difference between the two dates, in TOTAL YEARS, and at that it will be WRONG before that anniversay of your birth.

My birthday is 10/17/1944 making me 61 years 6 months and 11 days old at the moment, but

select DateDiff(year, '10/17/1944', getdate()) returns 62

however if you want the precise age, in Years months and Days, you need to do some calculations in the SQL, like this:

select cast(DateDiff(day, mDOB, getdate())/365.245 as int) as years ,cast((DateDiff(day, mDOB, getdate())/365.245 - cast(DateDiff(day, mDOB, getdate())/365.245 as int)) * 12 as int) as months,
cast((((DateDiff(day, mDOB, getdate())/365.245 - cast(DateDiff(day, mDOB, getdate())/365.245 as int)) * 12) - cast((DateDiff(day, mDOB, getdate())/365.245 - cast(DateDiff(day,mDOB, getdate())/365.245 as int)) * 12 as int))* 30 as int) as days

this will return the correct number of Years, months and days as the 'exact' age.

AW
0

LVL 4

Expert Comment

ID: 16563979
Or you could just

SELECT CAST(GETDATE()-'28-Nov-2005' AS INT)

to get the age as a number of DAYS, and do what you like from there!
0

LVL 4

Expert Comment

ID: 16563991
or

SELECT CAST(GETDATE()-'28-Nov-2005' AS DECIMAL) /365.25

if you want it in years
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
###### Suggested Courses
Course of the Month13 days, 22 hours left to enroll