November 16, 2012 at 2:07 am
Simple way to compute your age accurately.
A lot of query not exactly compute the age? yes! because sometime they only compute the datediff between DOB and datenow and divide it to 365.25 days and as a result they get a number with decimal something like 25 is the age with .06 decimal (age=25.06).
In this query you exactly get the age as is it.
Example 1
DOB = 11/15/1987 and
datenow =11/15/2012 the result would be
AGE=25
Example 2
DOB = 11/14/1987 and
datenow =11/15/2012 the result would be
AGE=24
SO HERE ARE THE QUERY
DECLARE @DOB SMALLDATETIME
SELECT @DOB = '11/15/1987'
SELECT
CASE
WHEN MONTH(@DOB) <= MONTH(GETDATE()) AND DAY(@DOB) <=DAY(GETDATE()) THEN DATEDIFF(YY,@DOB,GETDATE())
ELSE DATEDIFF(YY,@DOB,GETDATE())-1
END AS AGE
HOPE I CAN HELP! 🙂
November 16, 2012 at 2:38 am
Heres another take on it using FLOOR to strip of the decimal places.
Declare @dob date = '14-nov-1987'
Print @dob
Print Datediff(D,@dob,getdate())
Select Floor(Datediff(D,@dob,getdate()) / 365.25) Years
, Floor(Datediff(D,@dob,getdate()) % 365.25) Days
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 16, 2012 at 6:46 am
It doesn't work correctly
martinez.math (11/16/2012)
Example 2DOB = 11/14/1987 and
datenow =11/15/2012 the result would be
AGE=24
should be AGE=25
but your code returns 24 (:
p.s. It computes my age incorrect 🙁
November 16, 2012 at 8:21 pm
Yes the result is 24 not 25 because the DOB is Not exactly the currentdate.
but if the DOB is equal to currentdate or after the currentdate the result must be 25.:-)
I review the query and this is working.
Are you sure you get the right query?
Below is the query.
DECLARE @DOB SMALLDATETIME
SELECT @DOB = '11/15/1987'
SELECT
CASE
WHEN MONTH(@DOB) >= MONTH(GETDATE()) AND DAY(@DOB) >=DAY(GETDATE()) THEN DATEDIFF(YY,@DOB,GETDATE())
ELSE DATEDIFF(YY,@DOB,GETDATE())-1
END AS AGE
November 16, 2012 at 11:59 pm
The key to finding age is to find the birthday for the current year, and subtract 1 from the difference in years if the current date is before the birthday this year.
Note that the code below computes the current year birthday for Feb 29 birthdays as Feb 28 for non-leap years and Feb 29 for leap years. If you don't like that method, feel free to write your own.
The concept of negative age doesn't seem to have any real world meaning, so it returns null if date of birth before current date.
selecta.DOB,
b.CurrDate,
Age =
datediff(yy,a.DOB,b.CurrDate) +
case
-- Age is null when DOB before current date
when datediff(dd,a.DOB,b.CurrDate) < 0
then null
-- Subtract 1 if current date before birthday in current year
when datediff(dd,dateadd(yy,datediff(yy,a.DOB,b.CurrDate),a.DOB),b.CurrDate) < 0
then -1 else 0 end
from
( --Test Date of Birth
select DOB = convert(date,'19600229')union all
select DOB = convert(date,'19521013')
) ajoin
( -- Test Current Dates
select CurrDate = convert(date,'19001027')union all
select CurrDate = convert(date,'20110227')union all
select CurrDate = convert(date,'20110228')union all
select CurrDate = convert(date,'20120228')union all
select CurrDate = convert(date,'20120229')union all
select CurrDate = convert(date,'20121012')union all
select CurrDate = convert(date,'20121013')
) bon month(a.DOB) = month(b.CurrDate)
order by
a.DOB,
b.CurrDate
Results:
DOB CurrDate Age
---------- ---------- -----------
1952-10-13 1900-10-27 NULL
1952-10-13 2012-10-12 59
1952-10-13 2012-10-13 60
1960-02-29 2011-02-27 50
1960-02-29 2011-02-28 51
1960-02-29 2012-02-28 51
1960-02-29 2012-02-29 52
November 17, 2012 at 12:59 am
martinez.math (11/16/2012)
Yes the result is 24 not 25 because the DOB is Not exactly the currentdate.but if the DOB is equal to currentdate or after the currentdate the result must be 25.:-)
I review the query and this is working.
Are you sure you get the right query?
Below is the query.
DECLARE @DOB SMALLDATETIME
SELECT @DOB = '11/15/1987'
SELECT
CASE
WHEN MONTH(@DOB) >= MONTH(GETDATE()) AND DAY(@DOB) >=DAY(GETDATE()) THEN DATEDIFF(YY,@DOB,GETDATE())
ELSE DATEDIFF(YY,@DOB,GETDATE())-1
END AS AGE
Your code returns 60 when it should return 59 on the day before the 60th birthday.
DECLARE @DOB SMALLDATETIME, @NOW SMALLDATETIME
-- Get the day after 60 years before today
select @DOB = dateadd(dd,+1,dateadd(yy,-60,getdate()))
select [DOB] = @DOB, [NOW] = getdate()
SELECT
CASE
WHEN MONTH(@DOB) >= MONTH(GETDATE()) AND DAY(@DOB) >=DAY(GETDATE()) THEN DATEDIFF(YY,@DOB,GETDATE())
ELSE DATEDIFF(YY,@DOB,GETDATE())-1
END AS AGE
Results:
DOB NOW
----------------------- -----------------------
1952-11-18 02:58:00 2012-11-17 02:57:36.847
AGE
-----------
60
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply