1

Possible Duplicate:
How to calculate age in T-SQL with years, months, and days

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.

Community
  • 1
  • 1
Nitesh Kumar
  • 875
  • 4
  • 20
  • 39

1 Answers1

2

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)'
Laszlo T
  • 1,165
  • 10
  • 22
  • 1
    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: http://en.wikipedia.org/wiki/ISO_8601 – Laszlo T Jun 27 '12 at 12:58