问题
在Mybatis中运行没有问题,将SQL放在navicat控制台中就报错,
SELECT EXPENSES_NAME,EXPENSES_AMOUNT,ACCT
FROM EXPEND
WHERE EXPENSES_TYPE='1' AND TO_CHAR(ACCT,'yyyy-MM')=TO_CHAR('2020-12-03','yyyy-MM')
ORDER BY EXPENSES_AMOUNT DESC
解决
原因是,2020-12-03在Mybatis中传如的是日期类型,那么,所以用TO_CHAR进行转换,而NAVICAT中调试的时候,而当TO_CHAR的第一个参数不是日期类型的时候,那么TO_CHAR就会将其视为数字转字符串,发现第一个参数不是数字,则报无效数字
如下
SELECT
TO_CHAR(203.650,'999.9')
FROM DUAL
为此在navicat中调试日期类型的时候,记得去掉TO_CHAR函数,如下,问题解决
SELECT EXPENSES_NAME,EXPENSES_AMOUNT,ACCT
FROM EXPEND
WHERE EXPENSES_TYPE='1' AND TO_CHAR(ACCT,'yyyy-MM')='2020-12'
ORDER BY EXPENSES_AMOUNT DESC