Query Help

[heydyrtt]

The query below works if the date is this month, example when I run the query and dates are 11/01/2004 it will show 4 for four days. But the dates that show up in query for last and prior months is shows a negative number like -6 for 10/11/2004. How can I correct this. I'm trying to get the number days its been since last visit and within the last 90 days which will be the date_call column.

SELECT client,date_call, CURDATE(),(DAY(CURDATE())- DAY(date_call))
FROM trouble_calls
WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) <= date_call
AND onsite_visit = 'T';

Heydyrtt

 

[ericbrunson]

That's because this: DAY(CURDATE())- DAY(date_call)
is not doing date math. It's taking todays day of the month, 5 and subtracting the day of the month from last month using simple integer subtraction. So a service date of Oct 30 results in 5 - 30, or -25.

Use some variation of date_sub( curdate(), date_call ) to get an interval, then coerce that to a number of days.