Function to Get Current Age in MS SQL

Posted: October 1, 2013 in SQL


Many times we need to calculate user or client age in our requirement. So, I’ve created a function to calculate someone’s age in years, months, and days (best way).


Calculate User Age in SQL

Calculate User Age in SQL

Using the Code

Copy and page the below function in SQL and you can use it as show below just you have to pass only DATE to function.


SELECT dbo.f_getage(CONVERT(DATETIME,’19/07/1983′,105))


      -- Add the parameters for the function here
      @dayOfBirth Datetime
RETURNS varchar(50)
      -- Declare the return variable here
      DECLARE @Age varchar(50)
      SET @Age = '';
      -- Add the T-SQL statements to compute the return value here
      DECLARE @today datetime, @thisYearBirthDay datetime
      DECLARE @years int, @months int, @days int 
      SELECT @today = GETDATE() 
      SELECT @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dayOfBirth, @today), @dayOfBirth) 
      SELECT @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay > @today THEN 1 ELSE 0 END) 
      SELECT @months = MONTH(@today - @thisYearBirthDay) - 1 
      SELECT @days = DAY(@today - @thisYearBirthDay) - 1 
      SET @Age = convert(varchar(3),@years) + ' Years ' + convert(varchar(3),@months) + ' Months ' + convert(varchar(3),@days) + ' days'
     -- Return the result of the function
     RETURN @Age

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s