InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 18:59:19 GMT20http://www.sqlservercentral.com/Forums/Topic1380374-392-1.aspx[quote][b]Jeff Moden (11/3/2012)[/b][hr][quote][b]Sean Lange (11/2/2012)[/b][hr][quote][b]CELKO (11/2/2012)[/b][hr][quote] If my birthday is in December (1983-12-12) and I just did the datediff function using CURRENT_TIMESTAMP and that birthdate...It would return that my age is 29 when really I would still be 28! That is why I have all those if statements! Because those account for those situations! [/quote]We have a DATE data type, the ANSI/ISO Standard CURRENT_TIMESTAMP, and we use the ISO-8601 date formats; I corrected your posting. Now it gets worse; which age system do you use? Asians count the year in which you are living (I am in my 66-th year) and Westerners count the last whole year you passed (I was 65 on 2012-01-24). DATEDIFF (YEAR, '1983-12-12', CAST(CURRENT_TIMESTAMP AS DATE)) = 29But:SELECT DATEDIFF (YEAR, '1947-02-24', CAST(CURRENT_TIMESTAMP AS DATE)) = 65Instead of all that casting and concatenation, you can use:DATEDIFF (YEAR, @dd, CAST(CURRENT_TIMESTAMP AS DATE)), CASE SIGN(MONTH(CURRENT_TIMESTAMP) - MONTH (@dd)) WHEN -1 THEN -1 ELSE 0 ENDLittle puzzle: replace the CASE expression with calls to SIGN() and ABS().[/quote]Of if you prefer the really simple method you can just get the months and do integer division.[code]declare @dd datetime = '1947-2-24'select datediff(month, @dd, CURRENT_TIMESTAMP)/12[/code][/quote]Nice try Sean, but it doesn't work in all cases. Please see the following.[code="sql"]DECLARE @DOB DATETIMESET @DOB = '2008-12-31'DECLARE @Now DATETIMESET @Now = '2009-12-30'select datediff(month, @DOB, @Now)/12[/code][/quote]DOH! I figured it was far to simple to work. ;-)Mon, 05 Nov 2012 07:23:58 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1380374-392-1.aspxOrdinarily, I'd agree, Eugene. One of the reasons why I put these types of things in functions is because of the "problem" with leap year dates. As Michael stated, the current function uses an aniversary date of Feb 28th for Feb 29th on non-leap years. If someone decides that it should really be Mar 1st, I only have one spot to go to to fix things instead of trying to find all the places where I may have used inline code.Mon, 05 Nov 2012 05:38:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1380374-392-1.aspxI don't think that function should worry about time part of date of birth. In SQL2008 you better just to use DATE type so the time wouldn't be present. Pre-2008 I would enforce that DOB DATETIME column would only contain date part, so I wouldn't worry about time in queries which uses it.Actually, I'm not even sure that UDF is really required here... I would probaley just use in-line cross apply, something like that:[code="sql"] -- here is a sample table with DOB as DATE only DECLARE @MySample TABLE (DOB DATE) INSERT @MySample VALUES ('5 Nov 2012'),('3 Nov 2014'),('5 Nov 2011'),('6 Nov 2011'),('4 Apr 1973') -- usually it would be single day and most likely it will be today... DECLARE @DateToday DATE = GETDATE() SELECT M.DOB ,AC.AGE FROM @MySample M CROSS APPLY (SELECT DATEDIFF(yy, M.DOB, @DateToday) - CASE WHEN DOB > @DateToday THEN NULL WHEN DATEADD(yy, DATEDIFF(yy, DOB, @DateToday), DOB) > @DateToday THEN 1 ELSE 0 END AGE ) AC[/code]Actually, if you want to calculate the AGE on the range of dates, you can use the following:[code="sql"] -- here is a sample table with DOB as DATE only DECLARE @MySample TABLE (DOB DATE) INSERT @MySample VALUES ('5 Nov 2012'),('3 Nov 2014'),('5 Nov 2011'),('6 Nov 2011'),('4 Apr 1973') -- Range of dates: DECLARE @Dates TABLE (OnDay DATE) INSERT @Dates VALUES ('5 Nov 2012'),('10 Nov 2020'),('5 Nov 1970'),('5 Nov 2011'),('4 Apr 2073') SELECT M.DOB ,D.OnDay ,AC.AGE FROM @MySample M CROSS JOIN @Dates D CROSS APPLY (SELECT DATEDIFF(yy, M.DOB, D.OnDay) - CASE WHEN DOB > D.OnDay THEN NULL WHEN DATEADD(yy, DATEDIFF(yy, DOB, D.OnDay), DOB) > D.OnDay THEN 1 ELSE 0 END AGE ) AC ORDER BY M.DOB, D.OnDay [/code]Mon, 05 Nov 2012 03:28:52 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1380374-392-1.aspx[quote][b]Michael Valentine Jones (11/4/2012)[/b][hr]Jeff,I see a couple of minor issues with the function:[/quote]Thanks for catching the mistake on my part :blush:, Michael. Thank you, also, for the corrected code which will work even i SQL Server 2000.I deleted the code in my previous post because I didn't want anyone to use it by mistake for the very reasons you've given.Sun, 04 Nov 2012 20:01:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1380374-392-1.aspxJeff,I see a couple of minor issues with the function:1. Results may be a little off if the @DOB and @Now are not exactly set to 00:00:00, so I recommend rounding them back to midnight. This would only be an issue on their birthday.2. Results are a little odd if @Now is less than @DOB, so I recommend returning a NULL in that case. The concept of negative age doesn't seem to have any real world meaning anyway. And yes, I left this out of the code I posted also. :ermm:Alternate version with suggested changes:[code="sql"]CREATE FUNCTION dbo.AgeInYears_mvj ( @DOB DATETIME, --Date of birth or date of manufacture @Now DATETIME --Usually, GETDATE() or CURRENT_TIMESTAMP but --can be any date source like a column. )RETURNS TABLE WITH SCHEMABINDING ASRETURNSELECT AgeInYears = CASE WHEN a.[Now] < a.[DOB] THEN null --If birthday hasn't happended yet this year, subtract 1. WHEN DATEADD(yy, DATEDIFF(yy, a.[DOB], a.[Now]), a.[DOB]) > a.[Now] THEN DATEDIFF(yy, a.[DOB], a.[Now]) - 1 ELSE DATEDIFF(yy, a.[DOB], a.[Now]) ENDFROM (SELECT [DOB] = dateadd(dd,datediff(dd,0,@DOB),0), [Now] = dateadd(dd,datediff(dd,0,@Now),0)) a;GO[/code][code="sql"]SELECT * FROM dbo.AgeInYears_MVJ('19601104 01:00','20121104')SELECT * FROM dbo.AgeInYears ('19601104 01:00','20121104')SELECT * FROM dbo.AgeInYears_MVJ('19601104','19601103')SELECT * FROM dbo.AgeInYears ('19601104','19601103')[/code]Results:[code="plain"]AgeInYears-----------52AgeInYears-----------51AgeInYears-----------NULLAgeInYears------------1[/code]Sun, 04 Nov 2012 17:41:53 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1380374-392-1.aspx{EDIT} Code removed for the reasons that Michael stated in the next post below. I didn't want anyone to use it by accident. Michael's code in the next post will work for all versions of SQL Server 2000 and up (with a little special handling in SQL Server 2000 as a correlated subquery instead of using CROSS APPLY).Apologies for the mistake on my part.Sun, 04 Nov 2012 09:35:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1380374-392-1.aspxThe key to finding age is to find the birthday for the current year, and subtract 1 from the difference in years if the current date is before the birthday this year.Note that the code below computes the current year birthday for Feb 29 birthdays as Feb 28 for non-leap years and Feb 29 for leap years. If you don't like that method, feel free to write your own. [code="sql"]select a.DOB, b.CurrDate, BirthdayCurrentYear = dateadd(yy,datediff(yy,a.DOB,b.CurrDate),a.DOB), Age = datediff(yy,a.DOB,b.CurrDate) + -- Subtract 1 if current date before birthday in current year case when b.CurrDate < dateadd(yy,datediff(yy,a.DOB,b.CurrDate),a.DOB) then -1 else 0 end from ( --Test Date of Birth select DOB = convert(date,'19600229') union all select DOB = convert(date,'19521013') ) a join ( -- Test Current Dates select CurrDate = convert(date,'20110227') union all select CurrDate = convert(date,'20110228') union all select CurrDate = convert(date,'20120228') union all select CurrDate = convert(date,'20120229') union all select CurrDate = convert(date,'20121012') union all select CurrDate = convert(date,'20121013') ) b on month(a.DOB) = month(b.CurrDate)order by a.DOB, b.CurrDate[/code]Results:[code="plain"]DOB CurrDate BirthdayCurrentYear Age---------- ---------- ------------------- -----------1952-10-13 2012-10-12 2012-10-13 591952-10-13 2012-10-13 2012-10-13 601960-02-29 2011-02-27 2011-02-28 501960-02-29 2011-02-28 2011-02-28 511960-02-29 2012-02-28 2012-02-29 511960-02-29 2012-02-29 2012-02-29 52[/code]Sun, 04 Nov 2012 01:45:04 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1380374-392-1.aspx[quote][b]Sean Lange (11/2/2012)[/b][hr][quote][b]CELKO (11/2/2012)[/b][hr][quote] If my birthday is in December (1983-12-12) and I just did the datediff function using CURRENT_TIMESTAMP and that birthdate...It would return that my age is 29 when really I would still be 28! That is why I have all those if statements! Because those account for those situations! [/quote]We have a DATE data type, the ANSI/ISO Standard CURRENT_TIMESTAMP, and we use the ISO-8601 date formats; I corrected your posting. Now it gets worse; which age system do you use? Asians count the year in which you are living (I am in my 66-th year) and Westerners count the last whole year you passed (I was 65 on 2012-01-24). DATEDIFF (YEAR, '1983-12-12', CAST(CURRENT_TIMESTAMP AS DATE)) = 29But:SELECT DATEDIFF (YEAR, '1947-02-24', CAST(CURRENT_TIMESTAMP AS DATE)) = 65Instead of all that casting and concatenation, you can use:DATEDIFF (YEAR, @dd, CAST(CURRENT_TIMESTAMP AS DATE)), CASE SIGN(MONTH(CURRENT_TIMESTAMP) - MONTH (@dd)) WHEN -1 THEN -1 ELSE 0 ENDLittle puzzle: replace the CASE expression with calls to SIGN() and ABS().[/quote]Of if you prefer the really simple method you can just get the months and do integer division.[code]declare @dd datetime = '1947-2-24'select datediff(month, @dd, CURRENT_TIMESTAMP)/12[/code][/quote]Nice try Sean, but it doesn't work in all cases. Please see the following.[code="sql"]DECLARE @DOB DATETIMESET @DOB = '2008-12-31'DECLARE @Now DATETIMESET @Now = '2009-12-30'select datediff(month, @DOB, @Now)/12[/code]Sat, 03 Nov 2012 10:49:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1380374-392-1.aspx[quote][b]CELKO (11/2/2012)[/b][hr][quote] If my birthday is in December (1983-12-12) and I just did the datediff function using CURRENT_TIMESTAMP and that birthdate...It would return that my age is 29 when really I would still be 28! That is why I have all those if statements! Because those account for those situations! [/quote]We have a DATE data type, the ANSI/ISO Standard CURRENT_TIMESTAMP, and we use the ISO-8601 date formats; I corrected your posting. Now it gets worse; which age system do you use? Asians count the year in which you are living (I am in my 66-th year) and Westerners count the last whole year you passed (I was 65 on 2012-01-24). DATEDIFF (YEAR, '1983-12-12', CAST(CURRENT_TIMESTAMP AS DATE)) = 29But:SELECT DATEDIFF (YEAR, '1947-02-24', CAST(CURRENT_TIMESTAMP AS DATE)) = 65Instead of all that casting and concatenation, you can use:DATEDIFF (YEAR, @dd, CAST(CURRENT_TIMESTAMP AS DATE)), CASE SIGN(MONTH(CURRENT_TIMESTAMP) - MONTH (@dd)) WHEN -1 THEN -1 ELSE 0 ENDLittle puzzle: replace the CASE expression with calls to SIGN() and ABS().[/quote]Of if you prefer the really simple method you can just get the months and do integer division.[code]declare @dd datetime = '1947-2-24'select datediff(month, @dd, CURRENT_TIMESTAMP)/12[/code]Fri, 02 Nov 2012 13:58:25 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1380374-392-1.aspx[quote] If my birthday is in December (1983-12-12) and I just did the datediff function using CURRENT_TIMESTAMP and that birthdate...It would return that my age is 29 when really I would still be 28! That is why I have all those if statements! Because those account for those situations! [/quote]We have a DATE data type, the ANSI/ISO Standard CURRENT_TIMESTAMP, and we use the ISO-8601 date formats; I corrected your posting. Now it gets worse; which age system do you use? Asians count the year in which you are living (I am in my 66-th year) and Westerners count the last whole year you passed (I was 65 on 2012-01-24). DATEDIFF (YEAR, '1983-12-12', CAST(CURRENT_TIMESTAMP AS DATE)) = 29But:SELECT DATEDIFF (YEAR, '1947-02-24', CAST(CURRENT_TIMESTAMP AS DATE)) = 65Instead of all that casting and concatenation, you can use:DATEDIFF (YEAR, @dd, CAST(CURRENT_TIMESTAMP AS DATE)), CASE SIGN(MONTH(CURRENT_TIMESTAMP) - MONTH (@dd)) WHEN -1 THEN -1 ELSE 0 ENDLittle puzzle: replace the CASE expression with calls to SIGN() and ABS().Fri, 02 Nov 2012 13:12:19 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1380374-392-1.aspxOk there is this that may be worth looking at [url]http://www.sqlservercentral.com/Forums/Topic1237043-392-1.aspx[/url]Which discusses a similar problem and may be adaptable to your needs.Fri, 02 Nov 2012 09:46:19 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1380374-392-1.aspxWhat I can say? Have you tried to search this site?Check it here:[url]http://www.sqlservercentral.com/Forums/Topic796803-338-1.aspx#bm796805[/url]As I said, you don't need to create function for this, especially the one you have which unnecessary converts dates to all other data types...Fri, 02 Nov 2012 09:40:20 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1380374-392-1.aspxI apologize, I did not state my need clearly...I know that you can do a datediff on the years to get the birthdate...But that is not entirely accurateCase in point:If my birthday is in December (12/12/1983) and I just did the datediff function using getdate() and that birthdate...It would return that my age is 29 when really I would still be 28! That is why I have all those if statements! Because those account for those situations!And with it being a scalar function, it is running way slow! Was just wondering if there was a different way to go about it and be as accurate!Fri, 02 Nov 2012 09:28:06 GMTasm1212http://www.sqlservercentral.com/Forums/Topic1380374-392-1.aspxYou don't need a function for this.Just use Datediff:select DATEDIFF(year,@in_DOB,GetDate())Fri, 02 Nov 2012 08:29:15 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1380374-392-1.aspxHey guys...I have this GET_PERSON_AGE Function ASCREATE FUNCTION [dbo].[GET_PERSON_AGE](@in_DOB AS datetime)returns intasbeginDECLARE @age int IF cast(datepart(m,getDate()) as int) > cast(datepart(m,@in_DOB) as int) SET @age = cast(datediff(yyyy,@in_DOB,getDate()) as int) else IF cast(datepart(m,getDate()) as int) = cast(datepart(m,@in_DOB) as int) IF datepart(d,getDate()) >= datepart(d,@in_DOB) SET @age = cast(datediff(yyyy,@in_DOB,getDate()) as int) ELSE SET @age = cast(datediff(yyyy,@in_DOB,getDate()) as int) -1 ELSE SET @age = cast(datediff(yyyy,@in_DOB,getDate()) as int) - 1 RETURN @age ENDGOThis is a scalar function and works to perfection with with one exception! IT RUNS SLLLLOOOWWW when calling it...I was reading up on inline table function and how it is faster, so I tried using the same code to build an inline table function but I never could get it parse correctly?Can someone help me please? Thanks in advance Fri, 02 Nov 2012 07:46:59 GMTasm1212