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

    Monday, March 31, 2008 1:36 PM

Answers

  • 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
    Moderator
  • Nice one !
    That's what I needed.
    Thanks.
    Wednesday, June 04, 2008 11:13 AM