mysql的select自动将字符串转成date，但是insert和select一块用的时候，就不会自动转换

1 在select中，mysql自动将字符串转成date类型

1.1 select中

SELECT RATE.RATE_TYPE
      ,'2022-01-13' AS ACCEPT_OFFER_DATE
      ,RATE.COMMERCIAN_UNIT
      ,RATE_NUMERATOR
      ,RATE_DENOMINATOR
      , CAST( RATE_NUMERATOR AS DECIMAL(12,6) ) / RATE_DENOMINATOR AS RATE
FROM (
SELECT 'YTD招聘完成率' AS RATE_TYPE
  , 'China' AS COMMERCIAN_UNIT
  , SUM( CASE WHEN A.WHETHER_FILL_UP_ONTIME IN ( '按时填补','超时填补' ) THEN A.CNT END ) AS RATE_NUMERATOR
  , SUM( CASE WHEN A.WHETHER_FILL_UP_ONTIME IN ( '按时填补','超时填补','超时未填补' ) THEN A.CNT END ) AS RATE_DENOMINATOR
FROM ADS_RCM_EXECUTION_dev A
WHERE 1 = 1 

-- AND A.ACCEPT_THE_OFFER_TIME >= MAKEDATE(STR_TO_DATE('2022-01-13','%Y-%m-%d'),1) 
AND A.ACCEPT_THE_OFFER_TIME >= MAKEDATE('2022-01-13',1) 
AND A.ACCEPT_THE_OFFER_TIME <= '2022-01-13') RATE
;

 

 

1.2  加入inser后就报错了

INSERT INTO ADS_RCM_EXECUTION_BY_BU_ROLLING_dev
(
    RATE_TYPE
  , ACCEPT_THE_OFFER_TIME
  , COMMERCIAN_UNIT
  , RATE_NUMERATOR
  , RATE_DENOMINATOR
  , RATE
)
SELECT RATE.RATE_TYPE
      ,'2022-01-13' AS ACCEPT_OFFER_DATE
      ,RATE.COMMERCIAN_UNIT
      ,RATE_NUMERATOR
      ,RATE_DENOMINATOR
      , CAST( RATE_NUMERATOR AS DECIMAL(12,6) ) / RATE_DENOMINATOR AS RATE
FROM (
SELECT 'YTD招聘完成率' AS RATE_TYPE
  , 'China' AS COMMERCIAN_UNIT
  , SUM( CASE WHEN A.WHETHER_FILL_UP_ONTIME IN ( '按时填补','超时填补' ) THEN A.CNT END ) AS RATE_NUMERATOR
  , SUM( CASE WHEN A.WHETHER_FILL_UP_ONTIME IN ( '按时填补','超时填补','超时未填补' ) THEN A.CNT END ) AS RATE_DENOMINATOR
FROM ADS_RCM_EXECUTION_dev A
WHERE 1 = 1 

-- AND A.ACCEPT_THE_OFFER_TIME >= MAKEDATE(STR_TO_DATE('2022-01-13','%Y-%m-%d'),1) 
AND A.ACCEPT_THE_OFFER_TIME >= MAKEDATE('2022-01-13',1) 
AND A.ACCEPT_THE_OFFER_TIME <= '2022-01-13') RATE
;

 

 

 

 

1.3 手动将MAKEDATE函数中字符串转成date，

INSERT INTO ADS_RCM_EXECUTION_BY_BU_ROLLING_dev
(
    RATE_TYPE
  , ACCEPT_THE_OFFER_TIME
  , COMMERCIAN_UNIT
  , RATE_NUMERATOR
  , RATE_DENOMINATOR
  , RATE
)
SELECT RATE.RATE_TYPE
      ,'2022-01-13' AS ACCEPT_OFFER_DATE
      ,RATE.COMMERCIAN_UNIT
      ,RATE_NUMERATOR
      ,RATE_DENOMINATOR
      , CAST( RATE_NUMERATOR AS DECIMAL(12,6) ) / RATE_DENOMINATOR AS RATE
FROM (
SELECT 'YTD招聘完成率' AS RATE_TYPE
  , 'China' AS COMMERCIAN_UNIT
  , SUM( CASE WHEN A.WHETHER_FILL_UP_ONTIME IN ( '按时填补','超时填补' ) THEN A.CNT END ) AS RATE_NUMERATOR
  , SUM( CASE WHEN A.WHETHER_FILL_UP_ONTIME IN ( '按时填补','超时填补','超时未填补' ) THEN A.CNT END ) AS RATE_DENOMINATOR
FROM ADS_RCM_EXECUTION_dev A
WHERE 1 = 1 

AND A.ACCEPT_THE_OFFER_TIME >= MAKEDATE(STR_TO_DATE('2022-01-13','%Y-%m-%d'),1) 
-- AND A.ACCEPT_THE_OFFER_TIME >= MAKEDATE('2022-01-13',1) 
AND A.ACCEPT_THE_OFFER_TIME <= '2022-01-13') RATE
;