MySQL Date Query

[gigantorTRON]

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?

[gigantorTRON]

Anyone??

[fenway]

I have no idea what you're trying to do.

[AndyB]

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.

[Barand]

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?

 

[gigantorTRON]

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.