caculate age from birthdate in SQL stored procedure

  • I would write something in a stored procedure.

    If age<23, but we don't have age colum, but only a birthdate column, I do by below,

    ROUND((DATEDIFF(mm,CAST([Birthdate] AS DATE),GETDATE()) / 12),0) <18 ,

    is this correct? I did a cast part because orginally this column is a varchar

    also the round part, I am not sure what does 0 mean?

    Thanks,

    or any other ways of rewriting the below.

  • Computing the age of someone is more difficult than it might seem when you take into account different month lengths, leap year, and other things.

    This function returns age in years.

    Age Function F_AGE_IN_YEARS:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

  • My rules of thumb for date/datetime data:

    1) always store in the appropriate data type (date / datetime)

    2) don't put functions on columns, so if you need e.g. 23 year old use

    where mydate > dateadd(yy, -23, getdate() )

    in stead of "where datediff(yy, mydate, getdate()) >= 23 " because the latter one is not considered to be sargeble and indexes on your mydate column cannot be used in an optimal way to solve this criteria.

    3) stick with your column in date/datetime data type as long as you can. Meaning some people tend to convert to (var)char data type because they want to hand over formatted data to their calling app. Let the calling app handle the formatting itself !

    4) PLEASE DO NOT PROVIDE A DATE/DATETIME HANDLING FUNCTION AS IT HIDES WHAT ONE REALLY WANTS TO DO FOR ANYONE TROUBLESHOOTING THE QUERY AND IT MAY EVEN PREVENT THE OPTIMIZER TO GENERATE AN OPTIMAL PLAN.

    ( shouting intended, but not in an offensive way! It is a warning. )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • You don't really need a function for this although you could certainly put it in a function for programming consistency.

    SELECT DATEDIFF(yy,BirthdayColumn,GETDATE())

    - CASE

    WHEN GETDATE() < DATEADD(yy,DATEDIFF(yy,BirthdayColumn,GETDATE()),BirthdayColumn)

    THEN 1

    ELSE 0

    END

    FROM dbo.SomeTable

    I agree with Johan, though. If you don't need the actual number of years to be displayed, his solution would be the better way to go.

    --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)

  • Thanks all for the ideas.

    The reason I use cast the birthdate is because the birthdate column is varchar in a staging table.

    The staging table comes from a ssis package load from flat file source.

    we load the raw data in and keep the default varchar50 to the staging table, and didn't do the data type conversion in ssis, for the raw data helps us later in troubleshooting.

    Then we use a stored procedure convert the data type and insert the data from the staging table into real target table.

    And also in the sproc we filter out the birthdate <23

  • I think that adding a WHERE in your stored procedure will work

    e.g.

    UPDATE table

    SET col1 = 'some value'

    WHERE DATEDIFF(yy,Birthdate,getdate()) < 23

    unless i miss something...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply