asked on

Issues with mySQL Date query

I setup an absentee page that displays all the users that are out. I setup a query to pull all the users gone for todays date. You can also select 7 and 30 days. The issue I am running into is if a user took off from 09/06 and returns on 09/15. The query would only show him being gone on 09/06. The next day if you look at the report it will not display them.

Here is the query. I know I have to do something with the return date field but having issues getting it working.

SELECT id, fullname, department, firstdate, returndate, comment from timeofflog
where 
firstdate BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND DATE_ADD(NOW(), INTERVAL $day_sel DAY)
and status = 'Approved' and deleted = 'no'
order by department, fullname

Open in new window

Ray Paseur

Please have a look at this article:
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tomas Helgi Johannsson

Hi!

This is due to the fact that you have a time-window range on firstdate with the range "yesterday" to "a in the future date from now + some days".
As the days go by your time window slides showing different date range and data as the date range did before. :)

You can have another "time-window" for the returndate and let these thow time sets intersect for some time-period if that is what you are looking for.

Something like this. (this needs to be adjusted to your needs :) )

SELECT id, fullname, department, firstdate, returndate, comment from timeofflog
where 
firstdate BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND DATE_ADD(NOW(), INTERVAL $day_sel DAY)
and returndate BETWEEN DATE_ADD(NOW(), INTERVAL + 7 DAY) AND DATE_ADD(NOW(), INTERVAL $day_sel+ 7 DAY)

and status = 'Approved' and deleted = 'no'
order by department, fullname

Open in new window

Regards,
Tomas Helgi

ats2012

ASKER

This worked perfect. Thank you

Ray Paseur

Great! Thanks for the points, ~Ray