# how to get date difference in terms of year,month,days

### Question

•

I am using oracle 10G DB as back end.I have two date fields in a table.

i have to get the premium_term i.e, the difference between the two dates(premium_paying_end_date-premium_paying_start_date).

The difference should show the year,month and no of days difference.

For example :

Difference should be : 23 Y : 4 M : 15 D (Y = years, M = months , D= days)

So please give me the solution for this.

Monday, March 31, 2008 1:36 PM

• I am NOT sure how Reubun's query is correct. Can you please explain how you mark that an answer

Anyway, if interested try the below

Code Snippet

CREATE FUNCTION fnGetDateDiffAsYMD (@FromDate AS DateTime,@ToDate AS DATETIME)

RETURNS VARCHAR(30)

AS

BEGIN

DECLARE @Year  INT

,@Month INT

,@Day   INT

IF @FromDate > @ToDate

RETURN 'From Date should be <= To Date'

SELECT @Year  = DATEDIFF(yyyy,@FromDate,@ToDate)-1

,@Month = 0

,@Day   = 0

IF @Year < 2

SET @Year = 0

WHILE (1=1)

BEGIN

IF DATEADD(yyyy,@Year+1,@FromDate) <= @ToDate

SET @Year = @Year +1

ELSE IF DATEADD(mm,(@Year*12)+@Month+1,@FromDate) <= @ToDate

SET @Month = @Month +1

ELSE

BEGIN

SET @Day = DATEDIFF(dd,DATEADD(mm,@Year*12+@Month,@FromDate),@ToDate)

BREAK

END

END

RETURN CAST(@YEAR AS VARCHAR) + 'Y:' + RIGHT('00'+CAST(@Month AS VARCHAR),2) + 'M:'+ RIGHT('00'+CAST(@Day AS VARCHAR),2) + 'D'

END

---- Test Data-----------------

SELECT '1984-10-14' AS 'From','2008-03-01' AS 'To' ,dbo.fnGetDateDiffAsYMD('1984-10-14','2008-03-01') AS 'Diff'

SELECT '2008-01-01' AS 'From','2008-01-01' AS 'To' ,dbo.fnGetDateDiffAsYMD('2008-01-01','2008-01-01') AS 'Diff' UNION ALL

SELECT '2008-01-01','2008-01-28',dbo.fnGetDateDiffAsYMD('2008-01-01','2008-01-28') UNION ALL

SELECT '2008-01-01','2008-02-01',dbo.fnGetDateDiffAsYMD('2008-01-01','2008-02-01') UNION ALL

SELECT '2008-01-01','2008-02-27',dbo.fnGetDateDiffAsYMD('2008-01-01','2008-02-27') UNION ALL

SELECT '2008-01-01','2008-03-01',dbo.fnGetDateDiffAsYMD('2008-01-01','2008-03-01') UNION ALL

SELECT '2008-01-25','2008-02-20',dbo.fnGetDateDiffAsYMD('2008-01-25','2008-02-20') UNION ALL

SELECT '2008-01-25','2008-03-20',dbo.fnGetDateDiffAsYMD('2008-01-25','2008-03-20') UNION ALL

SELECT '2006-10-01','2008-01-01',dbo.fnGetDateDiffAsYMD('2006-10-01','2008-01-01') UNION ALL

SELECT '2006-10-01','2008-01-28',dbo.fnGetDateDiffAsYMD('2006-10-01','2008-01-28') UNION ALL

SELECT '2006-10-01','2008-02-01',dbo.fnGetDateDiffAsYMD('2006-10-01','2008-02-01') UNION ALL

SELECT '2006-10-01','2008-02-27',dbo.fnGetDateDiffAsYMD('2006-10-01','2008-02-27') UNION ALL

SELECT '2006-10-01','2008-03-01',dbo.fnGetDateDiffAsYMD('2006-10-01','2008-03-01') UNION ALL

SELECT '2006-10-25','2008-02-20',dbo.fnGetDateDiffAsYMD('2006-10-25','2008-02-20') UNION ALL

SELECT '2006-10-25','2008-03-20',dbo.fnGetDateDiffAsYMD('2006-10-25','2008-03-20') UNION ALL

SELECT '2007-10-01','2008-01-01',dbo.fnGetDateDiffAsYMD('2007-10-01','2008-01-01') UNION ALL

SELECT '2007-10-01','2008-01-28',dbo.fnGetDateDiffAsYMD('2007-10-01','2008-01-28') UNION ALL

SELECT '2007-10-01','2008-02-01',dbo.fnGetDateDiffAsYMD('2007-10-01','2008-02-01') UNION ALL

SELECT '2007-10-01','2008-02-27',dbo.fnGetDateDiffAsYMD('2007-10-01','2008-02-27') UNION ALL

SELECT '2007-10-01','2008-03-01',dbo.fnGetDateDiffAsYMD('2007-10-01','2008-03-01') UNION ALL

SELECT '2007-10-25','2008-02-20',dbo.fnGetDateDiffAsYMD('2007-10-25','2008-02-20') UNION ALL

SELECT '2007-10-25','2008-03-20',dbo.fnGetDateDiffAsYMD('2007-10-25','2008-03-20') UNION ALL

SELECT '2007-01-01','2008-01-01',dbo.fnGetDateDiffAsYMD('2007-01-01','2008-01-01') UNION ALL

SELECT '2007-01-01','2008-01-28',dbo.fnGetDateDiffAsYMD('2007-01-01','2008-01-28') UNION ALL

SELECT '2007-01-01','2008-02-01',dbo.fnGetDateDiffAsYMD('2007-01-01','2008-02-01') UNION ALL

SELECT '2007-01-01','2008-02-27',dbo.fnGetDateDiffAsYMD('2007-01-01','2008-02-27') UNION ALL

SELECT '2007-01-01','2008-03-01',dbo.fnGetDateDiffAsYMD('2007-01-01','2008-03-01') UNION ALL

SELECT '2007-01-25','2008-02-20',dbo.fnGetDateDiffAsYMD('2007-01-25','2008-02-20') UNION ALL

SELECT '2007-01-25','2008-03-20',dbo.fnGetDateDiffAsYMD('2007-01-25','2008-03-20') UNION ALL

SELECT '1997-01-01','2008-01-01',dbo.fnGetDateDiffAsYMD('1997-01-01','2008-01-01') UNION ALL

SELECT '1997-01-01','2008-01-28',dbo.fnGetDateDiffAsYMD('1997-01-01','2008-01-28') UNION ALL

SELECT '1997-01-01','2008-02-01',dbo.fnGetDateDiffAsYMD('1997-01-01','2008-02-01') UNION ALL

SELECT '1997-01-01','2008-02-27',dbo.fnGetDateDiffAsYMD('1997-01-01','2008-02-27') UNION ALL

SELECT '1997-01-01','2008-03-01',dbo.fnGetDateDiffAsYMD('1997-01-01','2008-03-01') UNION ALL

SELECT '1997-01-25','2008-02-20',dbo.fnGetDateDiffAsYMD('1997-01-25','2008-02-20') UNION ALL

SELECT '1997-01-25','2008-03-20',dbo.fnGetDateDiffAsYMD('1997-01-25','2008-03-20') UNION ALL

SELECT '2008-02-21','2008-02-20',dbo.fnGetDateDiffAsYMD('2008-02-21','2008-02-20')

Tuesday, April 01, 2008 8:01 AM

### All replies

• With SQL Server you could use this:

Code Snippet

select cast(datediff(yyyy, '2006-01-01', '2008-01-01') as varchar) + 'Y : '

+ cast(datediff(mm, '2006-01-01', '2008-01-01') as varchar) + 'M : '

+ cast(datediff(dd, '2006-01-01', '2008-01-01') as varchar) + 'D'

however, I do not know what you would need to do in Oracle.

Monday, March 31, 2008 1:59 PM
• This is a T-SQL but not PL/SQL forum, so it is not a good idea to put it here. But I think you can use refer to a PL/SQL book to got it.

Following script in PL/SQL will get the day level difference between two date:

SELECT (d1-d2) vaule FROM DUAL;

and you can use the to_char,to_date function in PL/SQL to get the format you want.

Monday, March 31, 2008 3:25 PM
• I am NOT sure how Reubun's query is correct. Can you please explain how you mark that an answer

Anyway, if interested try the below

Code Snippet

CREATE FUNCTION fnGetDateDiffAsYMD (@FromDate AS DateTime,@ToDate AS DATETIME)

RETURNS VARCHAR(30)

AS

BEGIN

DECLARE @Year  INT

,@Month INT

,@Day   INT

IF @FromDate > @ToDate

RETURN 'From Date should be <= To Date'

SELECT @Year  = DATEDIFF(yyyy,@FromDate,@ToDate)-1

,@Month = 0

,@Day   = 0

IF @Year < 2

SET @Year = 0

WHILE (1=1)

BEGIN

IF DATEADD(yyyy,@Year+1,@FromDate) <= @ToDate

SET @Year = @Year +1

ELSE IF DATEADD(mm,(@Year*12)+@Month+1,@FromDate) <= @ToDate

SET @Month = @Month +1

ELSE

BEGIN

SET @Day = DATEDIFF(dd,DATEADD(mm,@Year*12+@Month,@FromDate),@ToDate)

BREAK

END

END

RETURN CAST(@YEAR AS VARCHAR) + 'Y:' + RIGHT('00'+CAST(@Month AS VARCHAR),2) + 'M:'+ RIGHT('00'+CAST(@Day AS VARCHAR),2) + 'D'

END

---- Test Data-----------------

SELECT '1984-10-14' AS 'From','2008-03-01' AS 'To' ,dbo.fnGetDateDiffAsYMD('1984-10-14','2008-03-01') AS 'Diff'

SELECT '2008-01-01' AS 'From','2008-01-01' AS 'To' ,dbo.fnGetDateDiffAsYMD('2008-01-01','2008-01-01') AS 'Diff' UNION ALL

SELECT '2008-01-01','2008-01-28',dbo.fnGetDateDiffAsYMD('2008-01-01','2008-01-28') UNION ALL

SELECT '2008-01-01','2008-02-01',dbo.fnGetDateDiffAsYMD('2008-01-01','2008-02-01') UNION ALL

SELECT '2008-01-01','2008-02-27',dbo.fnGetDateDiffAsYMD('2008-01-01','2008-02-27') UNION ALL

SELECT '2008-01-01','2008-03-01',dbo.fnGetDateDiffAsYMD('2008-01-01','2008-03-01') UNION ALL

SELECT '2008-01-25','2008-02-20',dbo.fnGetDateDiffAsYMD('2008-01-25','2008-02-20') UNION ALL

SELECT '2008-01-25','2008-03-20',dbo.fnGetDateDiffAsYMD('2008-01-25','2008-03-20') UNION ALL

SELECT '2006-10-01','2008-01-01',dbo.fnGetDateDiffAsYMD('2006-10-01','2008-01-01') UNION ALL

SELECT '2006-10-01','2008-01-28',dbo.fnGetDateDiffAsYMD('2006-10-01','2008-01-28') UNION ALL

SELECT '2006-10-01','2008-02-01',dbo.fnGetDateDiffAsYMD('2006-10-01','2008-02-01') UNION ALL

SELECT '2006-10-01','2008-02-27',dbo.fnGetDateDiffAsYMD('2006-10-01','2008-02-27') UNION ALL

SELECT '2006-10-01','2008-03-01',dbo.fnGetDateDiffAsYMD('2006-10-01','2008-03-01') UNION ALL

SELECT '2006-10-25','2008-02-20',dbo.fnGetDateDiffAsYMD('2006-10-25','2008-02-20') UNION ALL

SELECT '2006-10-25','2008-03-20',dbo.fnGetDateDiffAsYMD('2006-10-25','2008-03-20') UNION ALL

SELECT '2007-10-01','2008-01-01',dbo.fnGetDateDiffAsYMD('2007-10-01','2008-01-01') UNION ALL

SELECT '2007-10-01','2008-01-28',dbo.fnGetDateDiffAsYMD('2007-10-01','2008-01-28') UNION ALL

SELECT '2007-10-01','2008-02-01',dbo.fnGetDateDiffAsYMD('2007-10-01','2008-02-01') UNION ALL

SELECT '2007-10-01','2008-02-27',dbo.fnGetDateDiffAsYMD('2007-10-01','2008-02-27') UNION ALL

SELECT '2007-10-01','2008-03-01',dbo.fnGetDateDiffAsYMD('2007-10-01','2008-03-01') UNION ALL

SELECT '2007-10-25','2008-02-20',dbo.fnGetDateDiffAsYMD('2007-10-25','2008-02-20') UNION ALL

SELECT '2007-10-25','2008-03-20',dbo.fnGetDateDiffAsYMD('2007-10-25','2008-03-20') UNION ALL

SELECT '2007-01-01','2008-01-01',dbo.fnGetDateDiffAsYMD('2007-01-01','2008-01-01') UNION ALL

SELECT '2007-01-01','2008-01-28',dbo.fnGetDateDiffAsYMD('2007-01-01','2008-01-28') UNION ALL

SELECT '2007-01-01','2008-02-01',dbo.fnGetDateDiffAsYMD('2007-01-01','2008-02-01') UNION ALL

SELECT '2007-01-01','2008-02-27',dbo.fnGetDateDiffAsYMD('2007-01-01','2008-02-27') UNION ALL

SELECT '2007-01-01','2008-03-01',dbo.fnGetDateDiffAsYMD('2007-01-01','2008-03-01') UNION ALL

SELECT '2007-01-25','2008-02-20',dbo.fnGetDateDiffAsYMD('2007-01-25','2008-02-20') UNION ALL

SELECT '2007-01-25','2008-03-20',dbo.fnGetDateDiffAsYMD('2007-01-25','2008-03-20') UNION ALL

SELECT '1997-01-01','2008-01-01',dbo.fnGetDateDiffAsYMD('1997-01-01','2008-01-01') UNION ALL

SELECT '1997-01-01','2008-01-28',dbo.fnGetDateDiffAsYMD('1997-01-01','2008-01-28') UNION ALL

SELECT '1997-01-01','2008-02-01',dbo.fnGetDateDiffAsYMD('1997-01-01','2008-02-01') UNION ALL

SELECT '1997-01-01','2008-02-27',dbo.fnGetDateDiffAsYMD('1997-01-01','2008-02-27') UNION ALL

SELECT '1997-01-01','2008-03-01',dbo.fnGetDateDiffAsYMD('1997-01-01','2008-03-01') UNION ALL

SELECT '1997-01-25','2008-02-20',dbo.fnGetDateDiffAsYMD('1997-01-25','2008-02-20') UNION ALL

SELECT '1997-01-25','2008-03-20',dbo.fnGetDateDiffAsYMD('1997-01-25','2008-03-20') UNION ALL

SELECT '2008-02-21','2008-02-20',dbo.fnGetDateDiffAsYMD('2008-02-21','2008-02-20')

Tuesday, April 01, 2008 8:01 AM
• I agreed sên, Rueben's response would not provide a workable or correct solution IF the question was a T_SQL question. (Rueben failed to take into account that Months would need to have the Years subtracted, and Days would have to have both Years and Months subtracted in order to provide the proper count of each component. In his solution, each component included the count of the higher level components. A mistake that I'm sure will help him be more careful in his presumptions about how to solve future problems.)

The OP did ask for a PL-SQL solution. He/she 'should' have been referred to an appropriate Oracle forum/newsgroup.

However, if someone using SQL Server wanted to solve the problem that was presented, your suggestion is quite workable. Nicely done.

This is the type of Function every one 'should' have in their 'box of tricks'.

Tuesday, April 01, 2008 6:03 PM
• Nice one !
That's what I needed.
Thanks.
Wednesday, June 04, 2008 11:13 AM