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
大神们帮忙看看