/*----------------------------------------------------------------------------------------------------------------------------
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())
Select DBO.FN_GETDATEDIFFTEXT('24-feb-1983', Getdate())
No comments:
Post a Comment