Calculating years, months and days between two dates

This script returns the number of years, months and days between dates given in parameters.

SELECT    TRUNC (MONTHS_BETWEEN (:end_date, :start_date) / 12)

       || ‘ YEARS ‘

       || MOD (TRUNC (MONTHS_BETWEEN (:end_date, :start_date)), 12)

       || ‘ MONTHS ‘

       || (  TO_DATE (:end_date)

           – ADD_MONTHS (:start_date,

                         TRUNC (MONTHS_BETWEEN (:end_date, :start_date))

                        )

          )

       || ‘ DAYS’

  FROM DUAL;

About these ads
By Ankur Bhatia Posted in Oracle

10 comments on “Calculating years, months and days between two dates

  1. || ( TO_DATE (:end_date)

    – ADD_MONTHS (:start_date,

    TRUNC (MONTHS_BETWEEN (:end_date, :start_date))

    )

    )

    i got error : ORA-0091 : INVALID CHARACTER ???

    • If you’re running it in, say Toad, write it like this:
      SELECT TRUNC (MONTHS_BETWEEN (:end_date, :start_date) / 12) YEARS,
      MOD (TRUNC (MONTHS_BETWEEN (:end_date, :start_date)), 12) MONTHS,
      ( TO_DATE (:end_date) – ADD_MONTHS (:start_date, TRUNC (MONTHS_BETWEEN (:end_date, :start_date)))) DAYS

      FROM DUAL;

  2. Error, example

    a) select fn_datedifi(to_date(’31/08/2011′, ‘dd/mm/yyyy’), to_date(’30/09/2011′, ‘dd/mm/yyyy’))
    from dual;

    return 1 month, 1 day

    b) select fn_datedifi(to_date(’30/08/2011′, ‘dd/mm/yyyy’), to_date(’30/09/2011′, ‘dd/mm/yyyy’))
    from dual;

    return 1 month, 1 day instead of 1 month, 1 dya

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s