Reputation: 4514
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
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
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