Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Calculating age Expand / Collapse
Author
Message
Posted Friday, January 9, 2009 11:24 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 19, 2012 12:37 AM
Points: 2,167, Visits: 149
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..
Post #634095
Posted Saturday, January 10, 2009 12:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,525, Visits: 4,071
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 :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #634103
Posted Friday, November 16, 2012 1:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 19, 2013 12:26 AM
Points: 14, Visits: 52
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!
Post #1385543
Posted Friday, November 16, 2012 3:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 5,035, Visits: 11,772
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!



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1385570
Posted Friday, November 16, 2012 9:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 15, 2014 4:01 AM
Points: 2,108, Visits: 1,706
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
Post #1385741
Posted Saturday, September 7, 2013 3:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 9, 2013 9:05 AM
Points: 1, Visits: 7
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
Post #1492487
Posted Saturday, September 7, 2013 3:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 36,958, Visits: 31,465
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1492557
Posted Saturday, September 7, 2013 3:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 36,958, Visits: 31,465
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1492561
Posted Saturday, September 7, 2013 3:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 36,958, Visits: 31,465
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1492563
Posted Saturday, September 7, 2013 4:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 36,958, Visits: 31,465
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1492565
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse