This function is written in MYSQL and tested in MYSQL 5.1. The function must be input with three parameters (for difference, return time difference year, time difference month... time Difference second), start time (used for calculation), End Time (used for calculation) No DELIMITER $ CREATEFUNCTIONDateBetween (ATypeINT, ADateStartDATETIME, ADateEndDATETIME) R
This function is written in MYSQL and tested in MYSQL 5.1. The function must be input with three parameters (for difference, return time difference year, time difference month... time Difference second), start time (used for calculation), End Time (used for calculation) No DELIMITER $ create function DateBetween (AType INT, ADateStart DATETIME, ADateEnd DATETIME) R
This function is written in MYSQL and tested in MYSQL 5.1.
The function must be input with three parameters, namely, the start time (used for calculation), the end time (used for calculation), and the return time (used for calculation) <无>
DELIMITER $ create function DateBetween (AType INT, ADateStart DATETIME, ADateEnd DATETIME) returns bigintbegindeclare AResult bigint default-1;/* input parameter AType INT type, result type 1: Year 2: month 3: Day 4: hour 5: minute 6: Second (must be within 1-6, otherwise the result returns-1) * input parameter ADateStart DATETIME type, start Time (must be a correct date parameter) * input parameter ADateEnd DATETIME type, End Time (must be a correct date parameter) * return value BIGINT type, returns the specified time difference between the start time and the end time (returns the calculated time difference IF the result is successful, returns-1 IF the result fails) */IF ADateStart! = ''AND ADateEnd! = ''AND AType> = 1 AND AType <= 6 then case AType WHEN 1 then select timestampdiff (YEAR, ADateStart, ADateEnd) INTO AResult; WHEN 2 then select timestampdiff (MONTH, ADateStart, ADateEnd) INTO AResult; WHEN 3 then select Week (DAY, ADateStart, ADateEnd) INTO AResult; WHEN 4 then select hour (HOUR, ADateStart, ADateEnd) INTO AResult; WHEN 5 then select timestampdiff (MINUTE, ADateStart, ADateEnd) INTO AResult; WHEN 6 then select timestampdiff (SECOND, ADateStart, ADateEnd) INTO AResult; end case; end if; RETURN (AResult); END $
SELECT DateBetween(0, '2010-01-01 00:00:00', '2010-01-01 10:00:00');SELECT DateBetween(1, '2001-01-01', '2010-01-01');SELECT DateBetween(2, '2010-01-01', '2010-12-01');SELECT DateBetween(3, '2010-01-01', '2010-01-31');SELECT DateBetween(4, '2010-01-01 00:00:00', '2010-01-01 10:00:00');SELECT DateBetween(5, '2010-01-01 00:00:00', '2010-01-01 10:00:00');SELECT DateBetween(6, '2010-01-01 00:00:00', '2010-01-01 10:00:00');SELECT DateBetween(7, '2010-01-01 00:00:00', '2010-01-01 10:00:00');