Calculating age

Question

Post reply

Calculating age

yogaanand.me

SSCrazy

Points: 2717
More actions
January 9, 2009 at 11:24 pm

#133588

hi friends
i have Date of Birth column in my database , i dont have age column
i want to calculate accurate Age form DOB..
please help me..
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Garadin One Orange Chip Points: 29613 More actions · Answer 1

DECLARE @DOB datetime

SET @DOB = '1/20/1980'

SELECT CASE

WHEN DATEPART(DY,GETDATE()) >= DATEPART(DY,@DOB)

THEN DATEDIFF(YY,@DOB,GETDATE())

ELSE DATEDIFF(YY,@DOB,GETDATE())-1

END

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

math martinez SSC Veteran Points: 285 More actions · Answer 2

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! 🙂

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

martinez.math (11/16/2012)
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! 🙂

You just responded to a three-year-old post!

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Richard Warr SSCertifiable Points: 6967 More actions · Answer 4

Phil Parkin (11/16/2012)
You just responded to a three-year-old post!

So, just add 3 to the result and all will be well 😉

hariharannkl Newbie Points: 7 More actions · Answer 5

It is very simple, just use the following sql statement,

(DATEDIFF(YY,DOB,GETDATE()) - CASE

WHEN MONTH(DOB)<MONTH(GETDATE()) AND (MONTH(DOB)=MONTH(GETDATE()) OR DAY(DOB)>DAY(GETDATE())) THEN 0 ELSE 1

END)

Here DOB is Columname, and GETDATE() is a function which gives the current date.

Hariweblog.com

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

Garadin (1/10/2009)
DECLARE @DOB datetime SET @DOB = '1/20/1980' SELECT CASE WHEN DATEPART(DY,GETDATE()) >= DATEPART(DY,@DOB) THEN DATEDIFF(YY,@DOB,GETDATE()) ELSE DATEDIFF(YY,@DOB,GETDATE())-1
END

Oh, be careful now. I know this is an old post but the code above doesn't work 100% of the time. For example, it returns "0" years if @DOB = '2000-03-31' and the current date is '2001-03-31'. The problem is that DY contains different values for dates after 28 Feb for ALL leap years.

Try it yourself...

DECLARE @DOB datetime

,@Now datetime

SELECT @DOB = '3/31/2000'

,@Now = '3/31/2001'

SELECT CASE

WHEN DATEPART(DY,@Now) >= DATEPART(DY,@DOB)

THEN DATEDIFF(YY,@DOB,@Now)

ELSE DATEDIFF(YY,@DOB,@Now)-1

END

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

If you believe that people that were born on the last day of February on a leap year turn a year older on the last day of February on non-leap years, this appears to work although I'll admit that I've not tested very many possibilities.

DECLARE @DOB DATETIME

,@Now DATETIME

SELECT @DOB = '2000-02-29'

,@Now = '2001-02-28'

SELECT DATEDIFF(yy,@DOB,@Now)

- CASE

WHEN @Now < DATEADD(yy,DATEDIFF(yy,@DOB,@Now),@DOB)

THEN 1

ELSE 0

END

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

math martinez (11/16/2012)
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! 🙂

It also has a Leap Year bug. I changed GETDATE() in your code to @Now to make it simple to test. Notice that your code says a person is 1 year old for the given dates, which is incorrect..

DECLARE @DOB DATETIME

,@Now DATETIME

SELECT @DOB = '2000-03-31'

,@Now = '2001-03-30'

SELECT

CASE

WHEN MONTH(@DOB) >= MONTH(@Now) AND DAY(@DOB) >=DAY(@Now) THEN DATEDIFF(YY,@DOB,@Now)

ELSE DATEDIFF(YY,@DOB,@Now)-1

END AS AGE

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 9

hariharannkl (9/7/2013)
It is very simple, just use the following sql statement,
(DATEDIFF(YY,DOB,GETDATE()) - CASE
WHEN MONTH(DOB)<MONTH(GETDATE()) AND (MONTH(DOB)=MONTH(GETDATE()) OR DAY(DOB)>DAY(GETDATE())) THEN 0 ELSE 1
END)
Here DOB is Columname, and GETDATE() is a function which gives the current date.

It would appear that your's also has a Leap Year problem but in the opposite direction. Your's returns "0" for the following dates which is incorrect.

DECLARE @DOB DATETIME

,@Now DATETIME

SELECT @DOB = '2000-03-31'

,@Now = '2001-03-31'

SELECT DATEDIFF(YY,@DOB,@Now) - CASE

WHEN MONTH(@DOB)<MONTH(@Now) AND (MONTH(@DOB)=MONTH(@Now) OR DAY(@DOB)>DAY(@Now)) THEN 0 ELSE 1

END

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gilbert delarosa SSC-Addicted Points: 425 More actions · Answer 10

This seems to work

DECLARE @DOB DATETIME

SET @DOB='9/19/2000'

SELECT FLOOR(DATEDIFF(DD,@DOB,GETDATE())/365.25)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 11

gilbert delarosa (9/20/2013)
This seems to work
DECLARE @DOB DATETIME
SET @DOB='9/19/2000'
SELECT FLOOR(DATEDIFF(DD,@DOB,GETDATE())/365.25)

Good try but it doesn't work...

DECLARE @DOB DATETIME

SET @DOB='01/01/2013'

SELECT FLOOR(DATEDIFF(DD,@DOB,'01/01/2014')/365.25)

Results:

---------------------------------------

0

(1 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gilbert delarosa SSC-Addicted Points: 425 More actions · Answer 12

But they wouldn't be 1 until after their BDay

or you could just

DECLARE @DOB DATETIME

SET @DOB='01/01/2013'

SELECT FLOOR((DATEDIFF(DD,@DOB,'01/01/2014')+1)/365.25)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 13

gilbert delarosa (9/20/2013)
But they wouldn't be 1 until after their BDay
or you could just
DECLARE @DOB DATETIME
SET @DOB='01/01/2013'
SELECT FLOOR((DATEDIFF(DD,@DOB,'01/01/2014')+1)/365.25)

Still doesn't work. Last I heard, you gained a year on your birthday and not the day before (Feb 29 babies sometimes excluded). 🙂

DECLARE @DOB DATETIME

SET @DOB='01/01/2012'

SELECT FLOOR((DATEDIFF(DD,@DOB,'12/31/2012')+1)/365.25)

Results:

---------------------------------------

1

(1 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 14

declare @dob datetime,@now date

set @dob='1 jan 2012'

set @now = '31 dec 2012'

select

datepart(year,@now) - datepart(year,@dob)

+

case

when dateadd(day

,datepart(day,@dob)-1

,dateadd(month

,datepart(month,@dob)-1

,dateadd(year

,datepart(year,@now)-1800

,'1 jan 1800'

)

) > @now

then -1

else 0

end

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);