CODEWITHSUNDEEP
mysql
Rob Sedgwick
Rob Sedgwick

Reputation: 4514

Getting the number of days excluding years between two dates

I want to get the number of days ignoring year between two dates in MySql.

I am using the following query, which works fine when the dates are more than a year apart.

select dayofyear(from_days((to_days('2015-11-20') - to_days('2014-11-15'))))

select dayofyear(from_days((to_days('2019-11-20') - to_days('2014-11-15'))))

correctly both return 5

However when the two dates are in the same year

select dayofyear(from_days((to_days('2014-11-20') - to_days('2014-11-15'))))

this returns 0. I think this is because MySql has a problem with dates in years less than 100 (or at least the dayofyear function has).

Is there any way around this?

Upvotes: 2

Views: 181

Answers (2)

O. Jones
O. Jones

Reputation: 108839

Absent your willingness to specify what happens on leap years, one is forced to guess.

I think this works. http://sqlfiddle.com/#!2/043543/1/0

IF(timestampdiff(DAY, st, str_to_date(concat_ws('-',YEAR(st),MONTH(fi), DAY(fi)),'%Y-%m-%d')) >= 0,
   timestampdiff(DAY, st, str_to_date(concat_ws('-',YEAR(st),MONTH(fi), DAY(fi)),'%Y-%m-%d')),
   timestampdiff(DAY, st, str_to_date(concat_ws('-',YEAR(st)+1,MONTH(fi), DAY(fi)),'%Y-%m-%d'))) delta

It tacks the starting year onto the ending day and tries to compute the number of days. Then, if that comes up negative, it tacks the year after the starting year onto the starting day and computes the number of days.

Upvotes: 1

Jean-François Savard
Jean-François Savard

Reputation: 21004

You are complicating this issue for nothing;

There is a DATEDIFF function in MySQL that allow you to do that.

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

That would return 2 (days).

To get the result regardless of the year, then apply % 365.

Edit : If you want to get the exact number of days considering leap years, you could substract to the number of days, the number of days for both year at 1st january (or any other date) :

SELECT DATEDIFF('2006-04-03','2006-04-01') - DATEDIFF(concat(year(yourDate), '01-01'), concat(year(yourDate2), '01-01'));

This will substract the total number of days between 1st january of each year to the total number of days.

Upvotes: 1

Related Questions