InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 23:38:57 GMT20http://www.sqlservercentral.com/Forums/Topic928705-392-1.aspxMy two bits...[quote]declare @dob datetimeset @dob = '2/29/2000'select convert(char(10),@dob,101) as DOB, case when ((MONTH(@dob) * 100) + DAY(@dob)) > ((MONTH(getdate()) * 100) + DAY(getdate())) then DATEDIFF(year,@dob,getdate()) - 1 else DATEDIFF(year,@dob,getdate()) End as AgeInYears[/quote]Tue, 19 Feb 2013 15:44:01 GMTjshahanhttp://www.sqlservercentral.com/Forums/Topic928705-392-1.aspx[quote][b]Jeff Moden (9/25/2012)[/b][hr][quote][b]Michael Valentine Jones (9/25/2012)[/b][hr]You can use the function on the link below to find the age:Age Function F_AGE_IN_YEARS[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462[/url][/quote]Any chance of making it NOT a scalar function?[/quote][code="sql"]SELECT DATEDIFF(yy, 0, GETDATE() - DOB) Age_Way1, YEAR(GETDATE()-DOB) -1900 Age_Way2 [/code]Sun, 17 Feb 2013 18:59:12 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic928705-392-1.aspx[quote][b]Jeff Moden (9/25/2012)[/b][hr][quote][b]Michael Valentine Jones (9/25/2012)[/b][hr]You can use the function on the link below to find the age:Age Function F_AGE_IN_YEARS[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462[/url][/quote]Any chance of making it NOT a scalar function?[/quote]I originally wrote this for SQL 2000, so be my guest. :-) The code could be greatly simplified too, probably enough to convert it to inline code.I posted it mainly to give them an example of some code that I know I tested fairly completely.Tue, 25 Sep 2012 15:18:28 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic928705-392-1.aspx[quote][b]Jeff Moden (9/25/2012)[/b][hr]Careful now... Because of the date conversions to the CHAR datatype, that will be relatively slow.[/quote]It is beautiful though - this has long been a favourite of mine (I think it was Rob Farley that first showed it to me).As far as performance is concerned, well yes it will be slow. But then, all [i]T-SQL[/i] solutions will be slow, right? ;-)Just kidding (mostly) - it's a good point that conversion to string is particularly bad. And especially scalar T-SQL UDFs :sick:Tue, 25 Sep 2012 14:42:36 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic928705-392-1.aspx[quote][b]Michael Valentine Jones (9/25/2012)[/b][hr]You can use the function on the link below to find the age:Age Function F_AGE_IN_YEARS[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462[/url][/quote]Any chance of making it NOT a scalar function?Tue, 25 Sep 2012 13:43:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic928705-392-1.aspx[quote][b]Adam Gojdas (5/28/2010)[/b][hr]Another way to calculate the age is:[code="sql"]SELECT (ABS(CAST(CONVERT(char(8),GETDATE(),112) AS int) - CAST(CONVERT(char(8),@DOB,112) AS int)) / 10000) [AGE];[/code]Here is a link to where I originally saw this type of calculation. It is in the comments section. :[url]http://www.sqlmag.com/article/tsql3/datetime-calculations-part-5.aspx[/url][/quote]Careful now... Because of the date conversions to the CHAR datatype, that will be relatively slow.Tue, 25 Sep 2012 13:39:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic928705-392-1.aspxYou can use the function on the link below to find the age:Age Function F_AGE_IN_YEARS[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462[/url]Tue, 25 Sep 2012 13:02:02 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic928705-392-1.aspxWayne,This doesn't work on leap years.Try these dates:@DOB = '09/26/2011'GetDate() = '09/25/2012'mitchTue, 25 Sep 2012 12:09:41 GMTmitch 14804http://www.sqlservercentral.com/Forums/Topic928705-392-1.aspxI'd second Lynn's article. It's a good discussion of this topic.Fri, 28 May 2010 09:26:14 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic928705-392-1.aspxYou can also check out this [url=http://www.sqlservercentral.com/articles/T-SQL/63351/][b]article[/b][/url] and the discussion that followed.Fri, 28 May 2010 09:21:32 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic928705-392-1.aspxAnother way to calculate the age is:[code="sql"]SELECT (ABS(CAST(CONVERT(char(8),GETDATE(),112) AS int) - CAST(CONVERT(char(8),@DOB,112) AS int)) / 10000) [AGE];[/code]Here is a link to where I originally saw this type of calculation. It is in the comments section. :[url]http://www.sqlmag.com/article/tsql3/datetime-calculations-part-5.aspx[/url]Fri, 28 May 2010 09:00:19 GMTAdam Gojdashttp://www.sqlservercentral.com/Forums/Topic928705-392-1.aspx[quote][b]WayneS (5/26/2010)[/b][hr][quote][b]ColdCoffee (5/26/2010)[/b][hr]You can use DATEDIFF function:LIke:[code="sql"]SET DATEFORMAT DMYSELECT DATEDIFF(yy,'21-03-1985',GETDATE()) AGE[/code][/quote]Since DateDiff returns the number of boundaries crossed in comparing these two dates, it will report the wrong age if today's date is not >= your DOB. So, use this instead:[code="sql"]SELECT CASE WHEN datepart(dayofyear, GetDate() ) >= datepart(dayofyear, @DOB) THEN DATEDIFF(yy,@DOB,GETDATE()) ELSE DATEDIFF(yy,@DOB,GETDATE()) -1 END AGE[/code][/quote]That perfectly makes sense! How sweet it is to start the day with learning a titbit.. wowThanks Wayne Shef! :-)Wed, 26 May 2010 21:57:33 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic928705-392-1.aspx[quote][b]ColdCoffee (5/26/2010)[/b][hr]You can use DATEDIFF function:LIke:[code="sql"]SET DATEFORMAT DMYSELECT DATEDIFF(yy,'21-03-1985',GETDATE()) AGE[/code][/quote]Since DateDiff returns the number of boundaries crossed in comparing these two dates, it will report the wrong age if today's date is not >= your DOB. So, use this instead:[code="sql"]SELECT CASE WHEN datepart(dayofyear, GetDate() ) >= datepart(dayofyear, @DOB) THEN DATEDIFF(yy,@DOB,GETDATE()) ELSE DATEDIFF(yy,@DOB,GETDATE()) -1 END AGE[/code]Wed, 26 May 2010 21:35:58 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic928705-392-1.aspxYou can use DATEDIFF function:LIke:[code="sql"]SET DATEFORMAT DMYSELECT DATEDIFF(yy,'21-03-1985',GETDATE()) AGE[/code]Wed, 26 May 2010 20:53:16 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic928705-392-1.aspxI have a table called member which contains memberid and DOB. How can I calculate age to date based on Date of Birth.Thanks,Wed, 26 May 2010 20:36:44 GMTQQ-485619