Date Calculation of 20 days

[ayesha789]

Hi, this is my query.
I calculate difference of 20 days from pDate(Process Date) form today.
But I want if rfDate(Resubmitted date) it will count from rfdate and ignore Pdate.

SELECT * 
FROM  `lims_payment` 
WHERE TO_DAYS( NOW( ) ) - TO_DAYS(  `pDate` ) >20
AND rfDate LIKE  '0000-00-00'

[ayesha789]

please ask me if you are unable to understand my code and logic

[rch1231]

I think this is what you are looking for:

Date arithmetic is less straightforward than time arithmetic due to the varying length of months and years, so MySQL provides special functions DATE_ADD( ) and DATE_SUB( ) for adding or subtracting intervals to or from dates. Each function takes a date value d and an interval, expressed using the following syntax:

DATE_ADD(d,INTERVAL val unit)
DATE_SUB(d,INTERVAL val unit)

Here, unit is the interval unit and val is an expression indicating the number of units. Some of the common unit specifiers are YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. (Check the MySQL Reference Manual for the full list.) Note that all these units are specified in singular form, not plural.

Using DATE_ADD( ) or DATE_SUB( ), you can perform date arithmetic operations such as the following:

· Determine the date three days from today:
mysql> SELECT CURDATE( ), DATE_ADD(CURDATE( ),INTERVAL 3 DAY);

[ayesha789]

my query is ok but there are some conditions like
if Payment is processed it calculates late payment when 20 more days gone.
But in case of payment return and then resubmitted in that case resubmitted date become process date.

[rch1231]

OK sorry about that I though you were having trouble with the calculation. I believe what you need is something like this for your where clause. I spread it out a bit so you would see the Parentheses and catch the order of precedence.

WHERE 
(
TO_DAYS( NOW( ) ) - TO_DAYS(  `pDate` ) >20
AND 
(rfDate LIKE  '0000-00-00' or rfDate is null)
)
OR
(
(rfDate not LIKE '0000-00-00' and rfDate is not null) 
AND 
TO_DAYS( NOW( ) ) - TO_DAYS(  `rfDate` ) >20
)