Age Calculate using Sql Query(Age Function in sql)

Step1 – Create Function fn_GetAge

 

 


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

 

ALTER FUNCTION [dbo].[fn_GetAge]

(@DOB as datetime, @now as datetime)

returns int

as

BEGIN

DECLARE @age INT

IF(@DOB IS NOT NULL)

BEGIN

SELECT @age =

CASE WHEN MONTH(@DOB) > MONTH(@now)

THEN DATEDIFF(YYYY,@DOB,@now)-1

WHEN MONTH(@DOB) < MONTH(@now)

THEN DATEDIFF(YYYY,@DOB, @now)

WHEN MONTH(@DOB) = MONTH(@now)

THEN

CASE WHEN DAY(@DOB) > DAY(@now)

THEN DATEDIFF(YYYY, @DOB, @now) - 1

ELSE

DATEDIFF(YYYY,@DOB, @now)

END

END

END

ELSE

SELECT @age = 0

RETURN @age

END

 

 

 

Step 2: Call this Functin

 


select dbo.[fn_GetAge]('1946-05-05','2011-03-13')

 

Advertisements
This entry was posted in Sql. Bookmark the permalink.

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