New Function

  • 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

  • You don't need a function for this.

    Just use Datediff:

    select DATEDIFF(year,@in_DOB,GetDate())

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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!

  • 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...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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

  • 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/

  • 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


    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.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • {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


    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.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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


    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.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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


    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.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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 DATETIME

    SET @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