Take the 2-minute tour ×

Possible Duplicate:

I have an application which calculates age on the specific date completely that is in years-months-days . Is there any simple way to calculate this in sql server 2005 . Actually I have created a function for this but that quite complex . I just want to know is there any simple way or any functionality in sql server from which I am not aware of?

The requirement is quite simple to calculate the exact age on the specific date for any applicant.

share|improve this question

marked as duplicate by bluefeet, Lamak, LittleBobbyTables, GarethD, kapa Jun 26 '12 at 17:06


What Have You Tried? –  N West Jun 26 '12 at 13:47
Looks like someone is having similar problem and may be relevant to the solutions you have found check this out stackoverflow.com/questions/9734089/… –  Siva Karthikeyan Jun 26 '12 at 13:49
How many days are there in a month? –  Mike Sherrill 'Cat Recall' Jun 26 '12 at 14:36
add comment

1 Answer

up vote 2 down vote accepted

Not sure if it's 100% correct, but something like this:

declare @d1 datetime, @d2 datetime, @y int, @m int, @d int
set @d1 = '2009-09-23'
set @d2 = '2012-04-23'

set @y = datediff(year, @d1, @d2) - 1
set @m = datediff(month, dateadd(year, @y, @d1), @d2) 
if dateadd(month, @m, dateadd(year, @y, @d1)) > @d2 set @m = @m - 1

set @d = datediff(day, dateadd(month, @m, dateadd(year, @y, @d1)), @d2)
print cast(@y as nvarchar) + ' year(s) ' + cast(@m as nvarchar) + ' month(s) and ' + cast(@d as nvarchar) + ' day(s)'
share|improve this answer
A minor point, but when working with dates it is advisable to either use non culture specific date formats (yyyyMMdd) or explicitly set the date format at the start of your query, e.g. SET DATEFORMAT YMD. This is especially true when answering here as it will ensure your examples will run no matter what server settings whoever is running it has. –  GarethD Jun 26 '12 at 16:08
Thanks, absolutely right. But AFAIK, I used the ISO format: en.wikipedia.org/wiki/ISO_8601 –  Laszlo Tenki Jun 27 '12 at 12:58
add comment
