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: 2 days ago @ 9:18 AM
Points: 1,519, Visits: 4,086
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: Yesterday @ 2:15 AM
Points: 5,317, Visits: 12,354
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1385570
Posted Friday, November 16, 2012 9:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 6:24 AM
Points: 2,227, Visits: 1,776
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: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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