gigantorTRON Posted February 2, 2008 Share Posted February 2, 2008 Hey all... I'm trying to pull a date query where an inspection date is compared to the current date and all items due for inspection are displayed. If the inspection date is Dec. 21, then the notification date should be Jan. 22 (there's no consistent day count which makes this harder). Here's the AND statements I have so far, but they don't work right. This is in mySQL 4. AND ((TO_DAYS(CURDATE())-(TO_DAYS(ps.PAD_Sites_Last_Inspected))) > cci.Corp_Customer_Info_CheckAED_Interval) " . "AND DATE_FORMAT(CURDATE(), '%m') > MONTH(ps.PAD_Sites_Last_Inspected) " . "AND CURDATE() > (INTERVAL 1 MONTH + ps.PAD_Sites_Last_Inspected) " . "AND CURDATE() > ps.PAD_Sites_Last_Inspected " . "ORDER BY mCust.Members_ID ASC, mPSC.Members_ID ASC, ps.PAD_Sites_Name ASC"; Is there an easy way to do this right? Quote Link to comment https://forums.phpfreaks.com/topic/88996-mysql-date-query/ Share on other sites More sharing options...
gigantorTRON Posted February 3, 2008 Author Share Posted February 3, 2008 Anyone?? Quote Link to comment https://forums.phpfreaks.com/topic/88996-mysql-date-query/#findComment-456903 Share on other sites More sharing options...
fenway Posted February 3, 2008 Share Posted February 3, 2008 I have no idea what you're trying to do. Quote Link to comment https://forums.phpfreaks.com/topic/88996-mysql-date-query/#findComment-457022 Share on other sites More sharing options...
AndyB Posted February 3, 2008 Share Posted February 3, 2008 If the inspection date is Dec. 21, then the notification date should be Jan. 22 (there's no consistent day count which makes this harder). Unless you mean one month plus one day later, I would have said that makes it near to impossible. Quote Link to comment https://forums.phpfreaks.com/topic/88996-mysql-date-query/#findComment-457069 Share on other sites More sharing options...
Barand Posted February 3, 2008 Share Posted February 3, 2008 What is the connection between inspection date and norification date? What is cci.Corp_Customer_Info_CheckAED_Interval if curdate() is > last_inspected + 1 month, then it's certainly > last_inspected - so why both conditions? Quote Link to comment https://forums.phpfreaks.com/topic/88996-mysql-date-query/#findComment-457120 Share on other sites More sharing options...
gigantorTRON Posted February 4, 2008 Author Share Posted February 4, 2008 What is cci.Corp_Customer_Info_CheckAED_Interval The interval is the number of days between tests. Some tests have to take place a shorter intervals than others. if curdate() is > last_inspected + 1 month, then it's certainly > last_inspected - so why both conditions? This should be >=... it's there because it need to be at least 1 month later. What I'm trying to do is a query that will show all 'sites' in my database that are due for inspection. The date that a site is 'due' is the day of the following month of inspection + 1 day. For example: last inspected Jan. 21, it should be due on Feb. 22. Quote Link to comment https://forums.phpfreaks.com/topic/88996-mysql-date-query/#findComment-457172 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.