sql日期时间值错误 大神帮忙看看这个是什么问题

Question

UPDATE f_get_exch_rt_date d 
SET d.V_EXCH_RATE_DATE = (
    SELECT
        T.REAL_DT 
    FROM
        fun_exch_rt_date T 
    WHERE
        T.CURRENCY1 = UPPER( d.P_CURRENCYTYPE1 ) 
        AND T.CURRENCY2 = UPPER( d.P_CURRENCYTYPE2 ) 
        AND DATE_FORMAT( T.MONTH, '%Y%m' ) = 
        DATE_FORMAT( DATE_SUB( DATE_FORMAT( d.P_MONTH, '%Y%m%d' ), INTERVAL 1 MONTH ), '%Y%m' ) 
     and T.MONTH is NOT null
        AND T.EXCH_RATE = (
        SELECT
            1/(case MIN( ts.EXCH_RATE )  when NULL and 0 then 0    else  MIN( ts.EXCH_RATE ) end)
        FROM
            fun_exch_rt_date ts 
        WHERE
            ts.CURRENCY1 = UPPER( d.P_CURRENCYTYPE2 ) 
            AND ts.CURRENCY2 = UPPER( d.P_CURRENCYTYPE1 ) 
            AND DATE_FORMAT( ts.MONTH, '%Y%m' ) = DATE_FORMAT( DATE_SUB( STR_TO_DATE(DATE_FORMAT( d.P_MONTH, '%Y%m%d' ),'%Y%m%d')
            , INTERVAL -1 MONTH ), '%Y%m' ) 
        ) 
    ) 
WHERE
    d.P_CURRENCYTYPE1 != d.P_CURRENCYTYPE2 
    AND d.V_EXCH_RATE_DATE IS NULL
    and d.P_MONTH IS NOT NULL

报出的问题是

> 1292 - Incorrect datetime value: '202003'

而拉出来 这个则能出结果

SELECT 
*
FROM
    f_get_exch_rt_date d,
    fun_exch_rt_date t 
WHERE
    DATE_FORMAT(date_sub( DATE_FORMAT( d.P_MONTH, '%Y%m%d' ), INTERVAL 1 MONTH ), '%Y%m' )= DATE_FORMAT(t.MONTH, '%Y%m' ) 
    AND d.P_MONTH IS NOT NULL

大神们帮忙看看

Answer 1

V_EXCH_RATE_DATE字段是date还是datetime类型，202003很明显是一个int类型，存入时间类型肯定不对的，将202003处理一下处理成2020-03-01就可以了