Function to return Age in years


How much year old customer is? Finance domain applications often use this logic to calculate the customer’s age. Here is the simple function that calculates the age in years.

Workaround
Input = DateOfBirth
Output = Returns the Years in Age

CREATE FUNCTION [dbo].[udf_GetAgeInYears]
(
      @sdtDateOfBirth SMALLDATETIME
)
RETURNS INT
AS
BEGIN
      DECLARE @iAge INT
      DECLARE @sdtToday SMALLDATETIME

      SET @sdtToday = CONVERT(SMALLDATETIME, GETDATE())

      SELECT @iAge =( (

             ( ( Year( @sdtToday ) * 365 ) + DatePart( dy,@sdtToday ) )

            -

             ( ( Year( @sdtDateOfBirth ) * 365 ) + DatePart( dy,@sdtDateOfBirth ) ) ) / 365 )

       RETURN @iAge
END
GO

How to execute = SELECT dbo.[udf_GetAgeInYears]('27-April-1983')
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