Finding month difference and then remaining days after months - mysql

Question

I need to find the difference between two dates to do some calculations based on the result.

Let's say column start_date is having value 1/Jan/2014 and column and end_date is having value 15/Mar/2014. The result I want is following format:

months | days_remain |
----------------------
   2          15

I can find MONTH difference and also DAY difference between separately (as 2 Months & 74 days) using TIMESTAMPDIFF function. But how to find out the remaining 15 days ?

Answer 1

This will work for sure.

Declare @StartDate datetime
Declare @EndDate datetime
Declare @years varchar(40)
Declare @months varchar(30)
Declare @days varchar(30)

set @StartDate ='2014/01/01'
set @EndDate = '2014/03/15'
select @years=datediff(year,@StartDate,@EndDate)
select @months=datediff(month,@StartDate,@EndDate)-(datediff(year,@StartDate,@EndDate)*12)
select @days=datepart(d,@EndDate)-datepart(d,@StartDate)
select @years  +' years,   ' +@months +' months,   '+@days   +' days' asYearMonthDay 

Answer 2

Try this. See the sample data as well:

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 
) sample_data;

Hope it helps.

Answer 3

I think this might be what you want. It does however return 14 days for remaining, but as Jaugar Chang pointed out in a comment that should be correct if as the difference between March 1st and March 15th is 14 days.

select
  timestampdiff(
    month, 
    start_date, 
    end_date
  ) as months,
  datediff(
    end_date,
    timestampadd(
      month, 
      timestampdiff(
        month, 
        start_date, 
        end_date
      )
      ,start_date
    )     
  ) as days_remain

from test;

Sample SQL Fiddle

Sample result:

|       START_DATE |       END_DATE | MONTHS | DAYS_REMAIN |
|------------------|----------------|--------|-------------|
| January, 01 2014 | March, 15 2014 |      2 |          14 |
| January, 10 2014 | March, 13 2014 |      2 |           3 |

Answer 4

You can use DATEDIFF to see the difference between 2 dates

SELECT DATEDIFF('2006-04-01','2005-04-01');

http://lists.mysql.com/mysql/196414

Answer 5

Try This, I hope it will Work For You.

select DateDiff(d, datepart(month,[Start_Date]),datepart(month,End_Date)) as  Months,

(30-day(end_date)) as Days_remain

from Sdate