Function to Get Current Age in MS SQL

Posted: October 1, 2013 in SQL

Introduction

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

Screenshot

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

—————————————————————————————–

CREATE FUNCTION [dbo].[F_GetAge]
(
      -- Add the parameters for the function here
      @dayOfBirth Datetime
)
RETURNS varchar(50)
AS
BEGIN
      -- 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
END
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s