Member Avatar for alex k

Show the details of sales orders which are paid more than 14 days after the date of the sales order.


SQL:
SELECT SO.OrderID, D.DistributorID, D.FirstName, D.SurName, SO.OrderDate, P.PaymentDate, P.PaymentAmount
FROM SalesOrder AS SO, Distributor AS D, Payment AS P
WHERE D.DistributorID=SO.DistributorID AND SO.OrderID=P.OrderID AND
DateDiff('yyyy',PaymentDate,Date( ) ) - DateDiff('yyyy',OrderDate,Date( )) <=14;

WHEN I RUN THIS QUERY IT RETURN ALL THE VLAUES OF ORDER..BUT I WANT TO RETURN THE ORDERS WHICH IS PAID AFTER 14 DAYS OR MORE ?
AND I TIRED THIS TOO (date()-P.PaymentDate)>=14; BUT IT DIDNT WORK :-(
ANY SUGGESTIONS ?PLS

  • 2 Contributors
  • 3 Replies
  • 154 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by Simon Tite
Member Avatar for alex k

I tried it ..it didnt work ..the problem is it doesnt accept two varaibles
it should be one constant to - it

Member Avatar for Simon Tite

.the problem is it doesnt accept two varaibles
it should be one constant to - it

Completely untrue.

From the MySql manual:

DATEDIFF(expr1,expr2)

DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
-> 1
mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
-> -31

The manual insists on 2 variables, the example I gave works. Paste the examples given by the MySql manual into phpmyadman, and you will see that it works. Oh, and make sure you type DATEDIFF in capitals.

The examples you have given contain (at least) three errors:-
* DateDiff instead of DATEDIFF
* First example has subtraction of two DateDiff functions, each containing 3 parameters. Should be one DATEDIFF function, containg two dates. No need to perform a subtraction yourself.
* Second example has one DateDiff function with one parameter.

If the correct way does not work, then your problem is not with the DATEDIFF function, but something else.

Edited by Simon Tite because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.