Get the difference between two dates both In Months and days in sql

Question

I need to get the difference between two dates say if the difference is 84 days  I should probably have output as 2 months and 14 days  the code I have just gives the totals  Here is the code  SELECT Months between To date  20120325    YYYYMMDD           To date  20120101    YYYYMMDD           num months           To date  20120325    YYYYMMDD   - To date  20120101    YYYYMMDD            diff in days FROM   dual     Output is   NUM MONTHS    DIFF IN DAYS 2 774193548       84   I need for example the output for this query to be either 2 months and 14 days at worst  otherwise I won t mind if I can have the exact days after the months figure because those days are not really 14 because all months do not have 30 days

User · Answer

MsSql Syntax   DATEDIFF   datepart   startdate   enddate    Oracle  This will returns number of days      select   round Second date - First date   as Diff InDays round   Second date - First date     30  1   as Diff InMonths round   Second date - First date     60 24  2   as TimeIn Minitues from       select     to date  01 01 2012 01 30 00 PM   mm dd yyyy hh mi ss am   as First date     to date  05 02 2012 01 35 00 PM   mm dd yyyy HH MI SS AM   as Second date   from     dual     result    Demo   http   sqlfiddle com   4 c26e8 36

User · Answer

Here I m just doing the difference between today  and a CREATED DATE DATE field in a table  which obviously is a date in the past   SELECT     FLOOR ABS MONTHS BETWEEN CREATED DATE  SYSDATE      12    12       months     AS MONTHS  -- we take total days - years as days  - months as days  to get remaining days FLOOR  SYSDATE - CREATED DATE  -      -- total days  FLOOR  SYSDATE - CREATED DATE  365  12   365 12  -      -- years  as days -- this is total months - years  as months   to get number of months   -- then multiplied by 30 416667 to get months as days  and remove it from total days  FLOOR   SYSDATE - CREATED DATE  365  12 -  FLOOR  SYSDATE - CREATED DATE  365  12      365 12        days   AS DAYS  FROM MyTable   I use  365 12   or 30 416667  as my conversion factor because I m using total days and removing years and months  as days  to get the remainder number of days   It was good enough for my purposes  anyway

User · Answer

The solution I post will consider a month with 30 days     select CONCAT  CONCAT  num months   MONTHS     CONCAT   days- num months  30    DAYS       from      SELECT floor Months between To date  20120325    YYYYMMDD       To date  20120101    YYYYMMDD        num months       To date  20120325    YYYYMMDD   - To date  20120101    YYYYMMDD        days   FROM   dual

User · Answer

I think that your question is not defined well enough  for the following reason   Answers relying on months between have to deal with the following issue  that the function reports exactly one month between 2013-02-28 and 2013-03-31  and between 2013-01-28 and 2013-02-28  and between 2013-01-31 and 2013-02-28  I suspect that some answerers have not used these functions in practice  or are now going to have to review some production code    This is documented behaviour  in which dates that are both the last in their respective months or which fall on the same day of the month are judged to be an integer number of months apart   So  you get the same result of  1  when comparing 2013-02-28 with 2013-01-28 or with 2013-01-31  but comparing it with 2013-01-29 or 2013-01-30 gives 0 967741935484 and 0 935483870968 respectively -- so as one date approaches the other the difference reported by this function can increase   If this is not an acceptable situation then you ll have to write a more complex function  or just rely on a calculation that assumes 30  for example  days per month  In the latter case  how will you deal with 2013-02-28 and 2013-03-31

User · Answer

is this what you ve ment    select trunc months between To date  20120325    YYYYMMDD   to date  20120101   YYYYMMDD     months               round To date  20120325    YYYYMMDD  -add months to date  20120101   YYYYMMDD                               trunc months between To date  20120325    YYYYMMDD   to date  20120101   YYYYMMDD       days         from dual

User · Answer

See the query below  assumed  dt1     dt2    Declare  dt1 datetime    2013-7-3  Declare  dt2 datetime    2013-5-2   select abs DATEDIFF DD   dt2   dt1   Days  case when  dt1  gt    dt2     then case when DAY  dt2  lt  DAY  dt1          then Convert varchar  DATEDIFF MONTH   dt2   dt1     CONVERT varchar    Month s       Convert varchar  DAY  dt1 -DAY  dt2     CONVERT varchar   Day s             else Convert varchar  DATEDIFF MONTH   dt2   dt1 -1    CONVERT varchar    Month s       convert varchar  abs DATEDIFF DD   dt1  DateAdd Month  -1   dt1    -  DAY  dt2 -DAY  dt1      CONVERT varchar   Day s         end     else  See asumption   dt1 must be  gt    dt2  end In Months Days   Returns    Days   In Months Days  62       2 Month s  1Day s

User · Answer

Updated for correctness  Originally answered by  jen   with DATES as      select TO DATE  20120101    YYYYMMDD   as Date1            TO DATE  20120325    YYYYMMDD   as Date2    from DUAL union all    select TO DATE  20120101    YYYYMMDD   as Date1            TO DATE  20130101    YYYYMMDD   as Date2    from DUAL union all    select TO DATE  20120101    YYYYMMDD   as Date1            TO DATE  20120101    YYYYMMDD   as Date2    from DUAL union all    select TO DATE  20130228    YYYYMMDD   as Date1            TO DATE  20130301    YYYYMMDD   as Date2    from DUAL union all    select TO DATE  20130228    YYYYMMDD   as Date1            TO DATE  20130401    YYYYMMDD   as Date2    from DUAL    MONTHS BTW as      select Date1  Date2            MONTHS BETWEEN Date2  Date1  as NumOfMonths    from DATES   select TO CHAR Date1   MON DD YYYY   as Date 1         TO CHAR Date2   MON DD YYYY   as Date 2         NumOfMonths as Num Of Months         TRUNC NumOfMonths  as  Month s           ADD MONTHS Date2  - TRUNC NumOfMonths   - Date1 as  Day s   from MONTHS BTW    SQLFiddle Demo          -------------- -------------- ----------------- ----------- --------          DATE 1         DATE 2       NUM OF MONTHS     MONTH S     DAY S         -------------- -------------- ----------------- ----------- --------        JAN 01 2012    MAR 25 2012    2 774193548387           2        24         JAN 01 2012    JAN 01 2013    12                      12         0         JAN 01 2012    JAN 01 2012    0                        0         0         FEB 28 2013    MAR 01 2013    0 129032258065           0         1         FEB 28 2013    APR 01 2013    1 129032258065           1         1        -------------- -------------- ----------------- ----------- --------    Notice  how for the last two dates  Oracle reports the decimal part of months  which gives days  incorrectly  0 1290 corresponds to exactly 4 days with Oracle considering 31 days in a month  for both March and April

User · Answer

Find out Year - Month- Day between two Days in Orale Sql    select  trunc trunc months between To date  20120101    YYYYMMDD   to date  19910228   YYYYMMDD     12  years   trunc months between To date  20120101    YYYYMMDD   to date  19910228   YYYYMMDD      -  trunc trunc months between To date  20120101    YYYYMMDD   to date  19910228   YYYYMMDD     12   12 months               round To date  20120101    YYYYMMDD  -add months to date  19910228   YYYYMMDD                               trunc months between To date  20120101    YYYYMMDD   to date  19910228   YYYYMMDD       days         from dual

User · Answer

SELECT    MONTHS BETWEEN date2 date1      datediff day date2 date1   30  as num months  datediff day date2 date1  as diff in days  FROM  dual      You should replace date2 with TO DATE  2012 03 25    YYYY MM DD      You should replace date1 with TO DATE  2012 01 01    YYYY MM DD      To get you results

User · Answer

select    dt1  dt2    trunc  months between dt2 dt1    mths     dt2 - add months  dt1  trunc months between dt2 dt1     days from       select date  2012-01-01  dt1  date  2012-03-25  dt2 from dual union all     select date  2012-01-01  dt1  date  2013-01-01  dt2 from dual union all     select date  2012-01-01  dt1  date  2012-01-01  dt2 from dual union all     select date  2012-02-28  dt1  date  2012-03-01  dt2 from dual union all     select date  2013-02-28  dt1  date  2013-03-01  dt2 from dual union all     select date  2013-02-28  dt1  date  2013-04-01  dt2 from dual union all     select trunc sysdate-1   dt1  sysdate               from dual   sample data   Results                            DT1                         DT2   MTHS       DAYS   ----------------------------------------------------------------------------    January  01 2012 00 00 00     March  25 2012 00 00 00      2         24      January  01 2012 00 00 00   January  01 2013 00 00 00     12          0      January  01 2012 00 00 00   January  01 2012 00 00 00      0          0     February  28 2012 00 00 00     March  01 2012 00 00 00      0          2     February  28 2013 00 00 00     March  01 2013 00 00 00      0          1     February  28 2013 00 00 00     April  01 2013 00 00 00      1          1       August  14 2013 00 00 00    August  15 2013 05 47 26      0   1 241273     Link to test  SQLFiddle

[sql] Get the difference between two dates both In Months and days in sql

Examples related to sql

Examples related to oracle

Examples related to date-arithmetic