Sign In | Feedback | Donate | Search
Katie and Emil Logo
Vote Yes! I like it! [+0]  |  Vote no! Dislike? [-0]
Author: by Emil Glownia

If you are after proper T-SQL Age calculation or Function then the below post might help you.

Below are questions that I try to answer in this post:

  • How to get age from birth date using getdate() (current date) in Tsql
  • How to calculate age from data of birth (DOB) and specified date
  • How to create T-SQL aging UDF function to calculate age from DOB and today's date or specified date
  • Calculate person's age (Accurate age calculation)

    T-SQL Age - years difference

    T-SQL Date of birth to Age



    T-SQL Age Calculation

    T-SQL Age Function (UDF)



    T-SQL Age Function

    T-SQL Age Calculation

      There are many example on the web of inaccurate calculation of age or date of birth. Some of them calculate it without taking into consideration month and day of month. Here is a link to one of these pages T-SQL Age (inaccurate) which tries to calculate age by dividing it by 365.25.

      Sometimes you might be after just a way to calculate difference in years and below is an example that you can use:

      SELECT DATEDIFF(Year,'1983-08-19',GETDATE()) AS AGE -- THIS IS DIFFERENCE IN YEARS

      But more ofter you want to calculate precise age and not accurate in 99% of cases but accurate in 100% of cases and below is how you can do that.

      If you would like to calcualte person's age using t-sql by converting person's date of birth to age then the below t-sql statement might help you.

      -- first we declare date of birth and the date we want to compare it to (usually today's date).
      DECLARE @DOB AS DATETIME
      SET @DOB = '1983-08-19'
      DECLARE @CompareDate AS DATETIME
      SET @CompareDate = GETDATE()

      -- below is t-sql statement that performs the actual conversion from date of birt to age.
      SELECT CASE WHEN (MONTH(@CompareDate)*100)+DAY(@CompareDate) >= (MONTH(@DOB)*100)+DAY(@DOB)
      THEN DATEDIFF(Year,@DOB,@CompareDate) ELSE DATEDIFF(Year,@DOB,@CompareDate)-1 END AS Age
      -- below example is the same above but based on field from database and getdate()
      SELECT CASE WHEN (MONTH(GETDATE())*100)+DAY(GETDATE()) >= (MONTH(DOB_Field)*100)+DAY(DOB_Field)
      THEN DATEDIFF(Year,DOB_Field,GETDATE()) ELSE DATEDIFF(Year,DOB_Field,GETDATE())-1 END AS Age

      -- and another t-sql statement but based on both dob field from database and comparedate from database as well.
      SELECT CASE WHEN (MONTH(CompareDate_Field)*100)+DAY(CompareDate_Field) >= (MONTH(DOB_Field)*100)+DAY(DOB_Field)
      THEN DATEDIFF(Year,DOB_Field,CompareDate_Field) ELSE DATEDIFF(Year,DOB_Field,CompareDate_Field)-1 END AS Age

      The link provides an alternative calculation T-SQL Age (also accurate)
      Below is T-SQL Age Function (UDF) which you can use and modify. Please be aware that it was created using SQL Server 2008 R2 function so it might not work on 2008 or 2005 without version adjustments.

      CREATE FUNCTION [dbo].[udfCalculateAge]
      (
      @DOB AS DATE,
      @EndDate as DATE = '2999-01-01' -- Defaul is today's date (see below) but any date can be used here
      )
      RETURNS TINYINT
      AS
      BEGIN
      DECLARE @Result as TINYINT

      -- IF DEFAULT VALUE (marked as 2999-01-01 as it doesn't accept functions) IS USED THEN USE TODAY'S DATE
      IF @EndDate = '2999-01-01'
      SET @EndDate = GETDATE()
      IF @DOB >= @EndDate -- trap errors
      SET @Result = 0
      ELSE
      BEGIN
      -- check if the person had its birthday in the specified year and calculate age
      IF (MONTH(@EndDate)*100)+DAY(@EndDate) >= (MONTH(@DOB)*100)+DAY(@DOB)
      SET @Result = DATEDIFF(Year,@DOB,@EndDate)
      ELSE
      SET @Result = DATEDIFF(Year,@DOB,@EndDate)-1
      END

      RETURN @Result

      END

      And below you can see the results

      Related Internal Links:
      T-SQL Current Date Related External Links:
      T-SQL DateDiff function
      T-SQL Day function
      T-SQL Month function
      Age (Wikipedia)
      Ageing (Wikipedia)


      Hope that helps!
      Emil
Upsss... Something went wrong and Google Ads on our website did not display.

Google ads sponsor FREE content on our website and without them it would not exist.

How to fix the problem?
  1. Check if the Google Search display any ads by searching the term Business Intelligence
    • If ads don't display, try different search terms and if there no ads then there might be a problem with your browser. If you made changes to your browser behaviour then undoing them will help.
    • If ads display then, refresh our page one more time, if the problem is still there than please contact us.
  2. Become 'No Ads Learner' Member or if you are already one Sign In.
Close
Take care
Katie & Emil
Well done! It seems Google Ads display properly now on our website.

Access our website's FREE content for FREE!