Getting Age in Text or Date Difference in text format

Anand Uchagawkar, 2011-10-03 (first published: 2011-08-30)

There might be many script used for finding the age, but this one I wrote as it is very to understand and simple to use. Here if anyone wants that the difference between two dates be calculated on basis on years, month and days passe between those two dates especially for dinding the age then this script is very handy.

Currently this script returns the difference text in format xx year(s), xx month(s), xx day(s)

So if anyone want to use this, it is very simple, Pass the From Date as first argument and To date as second argument it will return you the difference text

eg.

Select DBO.FN_GETDATEDIFFTEXT('30-Dec-2010', '31-Jan-2011') will give you result 0 year(s), 1 month(s), 1 day(s)

For finding Age of any person just pass the date of birth as first argument and GetDate() as second argument it will return you the age.

Select DBO.FN_GETDATEDIFFTEXT('01-Jan-1990', Getdate()) will give 21 year(s), 7 month(s), 29 day(s)

/*---------------------------------------------------------------------------------------------------------------------------- Author :-ANAND BABU UCHAGAWKAR Purpose:-To find the datediff/age in text format (eg. 1 year(s), 10 month(s), 10 day(s)). DATE:-30-Aug-2011 DATABASE:- SQL ----------------------------------------------------------------------------------------------------------------------------*/IF (Select COUNT(*) From Sysobjects Where [name] like 'FN_GETDATEDIFFTEXT') > 0 BEGIN DROP FUNCTION FN_GETDATEDIFFTEXT END GO CREATE FUNCTION FN_GETDATEDIFFTEXT(@FromDate DateTime, @ToDate DateTime) RETURNS NVARCHAR(50) AS BEGIN Declare @daysDiff Int Declare @monthDiff Int Declare @yearDiff Int --Select @daysDiff = DATEDIFF(DAY, @FromDate, @ToDate) Set @monthDiff = ABS(DATEDIFF(MONTH, @FromDate, @ToDate)%12) Set @yearDiff = ABS(DATEDIFF(YYYY, @FromDate, @ToDate)) -- If the From date month is greater than the month of the To date and the year difference is greater than zero -- then the year should the deducted by one IF DATEPART(MONTH,@FromDate) > DATEPART(MONTH,@ToDate) AND @yearDiff > 0 BEGIN Set @yearDiff = @yearDiff - 1 END IF DATEPART(DAY,@FromDate) > DATEPART(DAY, @ToDate) Begin --Get last date of the month of the FromDate Declare @lastDateOfMonth DateTime = DATEADD(MONTH, 1, @FromDate) Set @lastDateOfMonth = '01-' + DATENAME(MONTH,@lastDateOfMonth) + '-'+DATENAME(YEAR,@lastDateOfMonth) Set @lastDateOfMonth = DATEADD(DAY, -1, @lastDateOfMonth) Set @daysDiff = DATEDIFF(DAY, @FromDate, @lastDateOfMonth) Set @daysDiff = @daysDiff + DATEPART(DAY, @ToDate) Set @monthDiff = @monthDiff - 1 End ELSE BEGIN Set @daysDiff = DATEPART(DAY, @ToDate) - DATEPART(DAY, @FromDate) END -- Select @yearDiff Yr, @monthDiff Mn, @daysDiff Dy RETURN CAST(@yearDiff as nvarchar) + ' year(s), ' + CAST(@monthDiff as nvarchar) + ' month(s), ' + CAST(@daysDiff as nvarchar) + ' day(s)' END GO -- Select DBO.FN_GETDATEDIFFTEXT('30-Dec-2010', '31-Jan-2011') -- Select DBO.FN_GETDATEDIFFTEXT('01-Jan-1990', Getdate())