November 2, 2012 at 7:46 am
Hey guys...
I have this GET_PERSON_AGE Function AS
CREATE FUNCTION [dbo].[GET_PERSON_AGE](@in_DOB AS datetime)
returns int
as
begin
DECLARE @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
END
GO
This 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
November 2, 2012 at 8:29 am
You don't need a function for this.
Just use Datediff:
select DATEDIFF(year,@in_DOB,GetDate())
November 2, 2012 at 9:28 am
I 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 accurate
Case 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!
November 2, 2012 at 9:40 am
What I can say? Have you tried to search this site?
Check it here:
http://www.sqlservercentral.com/Forums/Topic796803-338-1.aspx#bm796805
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...
November 2, 2012 at 9:46 am
Ok there is this that may be worth looking at http://www.sqlservercentral.com/Forums/Topic1237043-392-1.aspx
Which discusses a similar problem and may be adaptable to your needs.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 2, 2012 at 1:58 pm
CELKO (11/2/2012)
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!
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)) = 29
But:
SELECT DATEDIFF (YEAR, '1947-02-24', CAST(CURRENT_TIMESTAMP AS DATE)) = 65
Instead 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 END
Little puzzle: replace the CASE expression with calls to SIGN() and ABS().
Of if you prefer the really simple method you can just get the months and do integer division.
declare @dd datetime = '1947-2-24'
select datediff(month, @dd, CURRENT_TIMESTAMP)/12
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 3, 2012 at 10:49 am
Sean Lange (11/2/2012)
CELKO (11/2/2012)
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!
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)) = 29
But:
SELECT DATEDIFF (YEAR, '1947-02-24', CAST(CURRENT_TIMESTAMP AS DATE)) = 65
Instead 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 END
Little puzzle: replace the CASE expression with calls to SIGN() and ABS().
Of if you prefer the really simple method you can just get the months and do integer division.
declare @dd datetime = '1947-2-24'
select datediff(month, @dd, CURRENT_TIMESTAMP)/12
Nice try Sean, but it doesn't work in all cases. Please see the following.
DECLARE @DOB DATETIME
SET @DOB = '2008-12-31'
DECLARE @Now DATETIME
SET @Now = '2009-12-30'
select datediff(month, @DOB, @Now)/12
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2012 at 1:45 am
The 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.
selecta.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')
) ajoin
( -- 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')
) bon month(a.DOB) = month(b.CurrDate)
order by
a.DOB,
b.CurrDate
Results:
DOB CurrDate BirthdayCurrentYear Age
---------- ---------- ------------------- -----------
1952-10-13 2012-10-12 2012-10-13 59
1952-10-13 2012-10-13 2012-10-13 60
1960-02-29 2011-02-27 2011-02-28 50
1960-02-29 2011-02-28 2011-02-28 51
1960-02-29 2012-02-28 2012-02-29 51
1960-02-29 2012-02-29 2012-02-29 52
November 4, 2012 at 9:35 am
{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.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2012 at 5:41 pm
Jeff,
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:
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 AS
RETURN
SELECT 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])
END
FROM
(SELECT[DOB] = dateadd(dd,datediff(dd,0,@DOB),0),
[Now] = dateadd(dd,datediff(dd,0,@Now),0)) a
;
GO
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')
Results:
AgeInYears
-----------
52
AgeInYears
-----------
51
AgeInYears
-----------
NULL
AgeInYears
-----------
-1
November 4, 2012 at 8:01 pm
Michael Valentine Jones (11/4/2012)
Jeff,I see a couple of minor issues with the function:
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2012 at 3:28 am
I 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:
-- 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
Actually, if you want to calculate the AGE on the range of dates, you can use the following:
-- 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
November 5, 2012 at 5:38 am
Ordinarily, 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2012 at 7:23 am
Jeff Moden (11/3/2012)
Sean Lange (11/2/2012)
CELKO (11/2/2012)
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!
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)) = 29
But:
SELECT DATEDIFF (YEAR, '1947-02-24', CAST(CURRENT_TIMESTAMP AS DATE)) = 65
Instead 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 END
Little puzzle: replace the CASE expression with calls to SIGN() and ABS().
Of if you prefer the really simple method you can just get the months and do integer division.
declare @dd datetime = '1947-2-24'
select datediff(month, @dd, CURRENT_TIMESTAMP)/12
Nice try Sean, but it doesn't work in all cases. Please see the following.
DECLARE @DOB DATETIMESET @DOB = '2008-12-31'
DECLARE @Now DATETIME
SET @Now = '2009-12-30'
select datediff(month, @DOB, @Now)/12
DOH! I figured it was far to simple to work. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply