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 9, 2009 11:24 PM
 SSCrazy Group: General Forum Members Last Login: Thursday, November 24, 2016 10:02 PM Points: 2,185, Visits: 162
 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: Tuesday, August 2, 2016 2:14 PM Points: 1,519, Visits: 4,107
 `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: Friday, November 13, 2015 7:02 PM Points: 15, Visits: 62
 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
 SSCertifiable Group: General Forum Members Last Login: Today @ 10:04 AM Points: 7,960, Visits: 19,072
Post #1385570
 Posted Friday, November 16, 2012 9:15 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, December 1, 2016 3:37 AM Points: 2,538, Visits: 1,979
 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 _____________________________________________________________________MCSA SQL Server 2012
Post #1385741
 Posted Saturday, September 7, 2013 3:29 AM
 Forum 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)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-Forever Group: General Forum Members Last Login: Today @ 8:45 AM Points: 42,066, Visits: 39,447
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1492557
 Posted Saturday, September 7, 2013 3:52 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 8:45 AM Points: 42,066, Visits: 39,447
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1492561
 Posted Saturday, September 7, 2013 3:59 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 8:45 AM Points: 42,066, Visits: 39,447
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1492563
 Posted Saturday, September 7, 2013 4:03 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 8:45 AM Points: 42,066, Visits: 39,447
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1492565

 Permissions