luffy luffy - 5 months ago 6
MySQL Question

Working with Date functions mysql and adding day to date while keeping track

So I have this table

CREATE TABLE `chittytransactions` (
`ChittyTransactionID` int(11) NOT NULL,
`AuctionID` int(11) NOT NULL,
`ChittyAccNo` int(11) DEFAULT NULL,
`Date` datetime DEFAULT NULL,
`Amount` double DEFAULT NULL,
`Description` varchar(50) DEFAULT NULL,
`TransRefence` varchar(50) DEFAULT NULL COMMENT 'Reference from actual Bank transaction',
`TransStatus` tinyint(1) DEFAULT NULL COMMENT 'If Transaction Pending or Cleared',
`ClearanceDate` datetime DEFAULT NULL,
`PaymentMethod` int(1) DEFAULT NULL COMMENT '0- Cash, 1- bank transfer, 2- personal credit etc'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Im creating a trigger that checks the
Date
and the
clearanceDate
to see if the
clearanceDate
is greater than the actual date then add a late fee. I have this so far:

DROP TRIGGER IF EXISTS chitty_before_trig;

DELIMITER ;;
CREATE TRIGGER chitty_before_trig BEFORE INSERT ON chittytransactions
FOR EACH ROW
BEGIN
DECLARE `userId` INT(11);

SELECT `UserId`
INTO `userId`
FROM chittyusers
WHERE ChittyAccNo = NEW.ChittyAccNo;

IF NEW.ClearanceDate <> NEW.`Date` THEN
UPDATE `chittyusers` SET LatePaymentFee = 50 WHERE UserId = userId;
END IF;
END;;
DELIMITER;


Using the date functions how can i check if a day has gone by etc and add a calculate late payment for each day?Please anything would be great Thank you.

Answer

If I've understood the question, then something like

IF NEW.ClearanceDate > NEW.`Date` THEN
  UPDATE chittyusers
    SET LatePaymentFee = 10 * DATEDIFF(NEW.ClearanceDate, NEW.`Date`)
    WHERE UserId = userId;
END IF;

would work with your current schema. DATEDIFF(date1, date2) returns the number of days between date1 and date2 - the result is negative if date1 is earlier than date2. Note I've changed the comparison operator from <> to > so the late fee is applied if ClearanceDate is after Date, but not if it's before.

I should point out that having a bare numeric constant like 10 in your code like that is considered very bad form. A slightly better way would be to declare a local variable named something like baseLateFee, set it to 10, and then use the variable in your calculation. A much better alternative, especially if there's any possibility that baseLateFee will ever change, would be to store it in a table somewhere so you can update it without having to find and change all the code that depends on it.

Comments