Date between 2 dates

[Danny159]

Hey Guys,

I need some help... I have the following code:

$dueinQ = mysql_query("SELECT * FROM `invoices` WHERE `status` = '10' OR `status` = '11' OR `status` = '9'");
while($dueinR = mysql_fetch_assoc($dueinQ)){

$inv_due = $dueinR['duedate'];
$eofy = $dueinR['eofy'];
$datediff = $inv_due - $eofy;
$days = floor($datediff/(60*60*24));

	if($days > 365){
		//Do nothing
	}else{
		$due = $due + ($dueinR['total'] - $dueinR['total_paid']);			
	}
}
echo $due;

but it doesn't work...

I am trying to work out if the due date of the invoice is in THIS financial year... if it is add it to the total else don't....
but its not working, I can seem to get it to work out if its this financial year (e.g. end of this financial year is 1/4/2012)

Dan

[niranga]

Try to get the date difference directly from the query like this

SELECT *, DATEDIFF(duedate,eofy) as date_diff FROM `invoices` WHERE `status` = '10' OR `status` = '11' OR `status` = '9'

Now in the while loop you can get the date difference in days from $dueinR. So you can perform the if else checking at the beginning of the while loop

The date diff will give the difference, so I don't know if that will work. Bepending on the format of your dates (should be yyyy-mm-dd), you could just do a simple comparison:

SELECT ... FROM `invoices` WHERE `status` > 8 AND `status` < 12 AND `dateinv` >= '2011-04-01'

[niranga]

@ardav : DATEDIFF gives the difference between two dates by number of days. It works when two dates are in the format of 'timestamp' data type(Ex: 2011-11-18 10:09:08). Actually I am not sure about other date formats as you pointed :)

EDIT: I think you are referring to 'duedate' by 'dateinv' in your reply

[ThLFR]

Hello,

Why not have MySQL do all the calculation with the following query?
SELECT sum( total - total_paid) as due FROM `invoices` WHERE `status` in( '9', '10', '11') and DATEDIFF(duedate,eofy) <= 365

The datediff(date1, date2) function returns date1 – date2 expressed as a value in days. BUT date1 and date2 have to be date or date-and-time expressions.

HTH

I'm not so keen on this datediff thingy - what about leap years? Changing to 366 won't help. Also you're making a calculation DATEDIFF (slow) where it is not required.

EDIT: I think you are referring to 'duedate' by 'dateinv' in your reply

Is that aimed at me? 'dateinv' in my example is just the invoice date. If the invoice date is since 2011-04-01, include it. Otherwise don't. Simple.

[niranga]

@ardav : In @Danny159's first code snippet he was trying to get the date difference between 'duedate' and 'eofy' where both were taken from the database. I am sorry if I understood that incorrectly :)

I don't think you did,I just don't understand why the eofy needs to be in every record. Doesn't make sense to me.

[niranga]

Yes.. I just noticed it.