Home > SQL Server > Calculate Age in Day-Month-Year using SQL Server Query

Calculate Age in Day-Month-Year using SQL Server Query

Hi all,

try this example to create SQL Server Stored Procdure to Calculate  Age in Day-Month-Year

create proc CalculateAge
@dayOfBirth datetime
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 
SELECT @years as years, @months as months, @days as days

and to use this Stored Procedure

exec CalculateAge '01/21/1984'

Hope this helps

Good Luck

Categories: SQL Server
  1. Nags
    September 7, 2012 at 9:28 am

    thank you. it is working effectively. but how to find the week day from the birthdate

  2. May 6, 2013 at 7:17 pm

    Hi there to all, how is everything, I think every one is getting
    more from this web page, and your views are good in support of new people.

  3. asharaf
    July 18, 2013 at 9:00 am

    result is
    Msg 8114, Level 16, State 5, Procedure age_date, Line 0
    Error converting data type varchar to datetime.

  4. February 8, 2014 at 9:39 am

    I honestly think that this is a very handsome page you
    have there. Would you approve if I posted your page on my facebook along with the url and header of the post:”Calculate Age in Day-Month-Year using SQL Server Query “??
    I visided this blog as I was browsing for cartilage jewelry and I think that I am very lucky for doing so.

    The Jewelry for Cartilage Team

  1. July 3, 2014 at 5:26 am
  2. July 5, 2014 at 5:41 am
  3. July 6, 2014 at 4:28 pm

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 )

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

%d bloggers like this: