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.

 

1)premium_paying_start_date

2)premium_paying_end_date

 

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 :

premium_paying_start_date : 14-10-1984

premium_paying_end_date : 01-03-2008

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

 

So please give me the solution for this.

Answer 1

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. 

Answer 2

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.

Answer 3

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')

Answer 4

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'.

 

Answer 5

Nice one !
That's what I needed.
Thanks.