Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculating age Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, January 09, 2009 11:24 PM
 SSCrazy 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 columni want to calculate accurate Age form DOB..please help me..
Post #634095
 Posted Saturday, January 10, 2009 12:27 AM
 SSCommitted Group: General Forum Members Last Login: Sunday, December 15, 2013 8:09 AM Points: 1,525, Visits: 4,060
 `DECLARE @DOB datetimeSET @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 PhelabaumConsistency 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 Group: General Forum Members Last Login: 2 days ago @ 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 1DOB = 11/15/1987 and datenow =11/15/2012 the result would be AGE=25 Example 2DOB = 11/14/1987 and datenow =11/15/2012 the result would be AGE=24 SO HERE ARE THE QUERYDECLARE @DOB SMALLDATETIMESELECT @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())-1END AS AGEHOPE I CAN HELP!
Post #1385543
 Posted Friday, November 16, 2012 3:00 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Yesterday @ 4:31 PM Points: 4,657, Visits: 10,634
 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 1DOB = 11/15/1987 and datenow =11/15/2012 the result would be AGE=25 Example 2DOB = 11/14/1987 and datenow =11/15/2012 the result would be AGE=24 SO HERE ARE THE QUERYDECLARE @DOB SMALLDATETIMESELECT @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())-1END AS AGEHOPE 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
 SSCommitted Group: General Forum Members Last Login: 2 days ago @ 7:43 AM Points: 1,855, Visits: 1,456
 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 07, 2013 3:29 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, September 09, 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)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 07, 2013 3:29 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 11:27 PM Points: 34,687, Visits: 28,878
 Garadin (1/10/2009)`DECLARE @DOB datetimeSET @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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1492557
 Posted Saturday, September 07, 2013 3:52 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 11:27 PM Points: 34,687, Visits: 28,878
 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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1492561
 Posted Saturday, September 07, 2013 3:59 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 11:27 PM Points: 34,687, Visits: 28,878
 math martinez (11/16/2012)SO HERE ARE THE QUERYDECLARE @DOB SMALLDATETIMESELECT @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())-1END AS AGEHOPE 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)-1END 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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1492563
 Posted Saturday, September 07, 2013 4:03 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 11:27 PM Points: 34,687, Visits: 28,878
 hariharannkl (9/7/2013)It is very simple, just use the following sql statement,(DATEDIFF(YY,DOB,GETDATE()) - CASE WHEN MONTH(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)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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1492565

 Permissions

 Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.