MYSQL STR_TO_DATE 未返回所需结果

Question

我正在尝试运行一个函数来返回两个日期之间的天数差。出于这个原因，我根据日期参数获取两个日期(这工作正常)。接下来，如果在表中找不到日期，我将使用参数 date pdate 来构造日期。这种日期结构没有按预期工作。以下是代码:

`DELIMITER $$

USE payrolldb001 $$

DROP FUNCTION IF EXISTS NoOfWorkingDays $$

CREATE DEFINER=`root`@`localhost` FUNCTION NoOfWorkingDays(P_EmployeeID INT,pdate             DATE) RETURNS INT(10) 
READS SQL DATA
BEGIN

DECLARE v_FromDate DATE;
DECLARE todate DATE;
DECLARE workingdays INT;
DECLARE shiftoff INT;
DECLARE gazzetted INT;

SET v_FromDate=(SELECT cp.FromDate FROM current_payroll cp
               INNER JOIN employees e ON e.employeeid=P_EmployeeID AND e.CompanyCode=cp.CompanyCode 
               AND e.SubOfficeCode=cp.SubOfficeCode
               WHERE pdate BETWEEN cp.FromDate AND cp.ToDate);
IF (v_FromDate IS NOT NULL) THEN              
SET todate=(SELECT cp.ToDate FROM current_payroll cp
               INNER JOIN employees e ON e.employeeid=P_EmployeeID AND e.CompanyCode=cp.CompanyCode 
               AND e.SubOfficeCode=cp.SubOfficeCode
               WHERE pdate BETWEEN cp.FromDate AND cp.ToDate);
    ELSE 
        SET v_FromDate=STR_TO_DATE(CONCAT(YEAR(pdate),'',MONTH(pdate)-1,'-21'),"%d%m%Y");
        SET todate=STR_TO_DATE(CONCAT(YEAR(pdate),'-',MONTH(pdate),'-20'),"%d%m%Y");

END IF;

SET workingdays = DATEDIFF(todate,v_FromDate);

WHILE  v_FromDate<=todate DO
     SET shiftoff= `isShiftOff`(P_EmployeeID,v_FromDate);
    IF (shiftoff=0) THEN
        SET gazzetted = `GazettedHoliday`(v_FromDate,P_Employeeid);
         SET workingdays = workingdays-gazzetted;
    ELSE
        SET workingdays=workingdays-shiftoff;
    END IF;

    -- RESET QUERY CACHE;
    -- FLUSH QUERY CACHE;
    SET v_FromDate = ADDDATE(v_FromDate,1); 
END WHILE;

RETURN workingdays ; 

END$$

DELIMITER ;`

我得到的错误如下:不正确的日期时间值:函数 str_to_date 的“2013-8-21”在 v_FromDate 的转换中。

Answer 1

解决方法是将日期变量的设置更改为:

SET v_FromDate=STR_TO_DATE(CONCAT(YEAR(pdate),'-',MONTH(pdate)-1,'-',21),"%Y-%c-%d");

SET todate=STR_TO_DATE(CONCAT(YEAR(pdate),'-',MONTH(pdate),'-',20),"%Y-%c-%d");