描述:
Sqlsever数据库中自带函数CONVERT()在金仓数据库中使用TO_DATE()。
原sql语句:
SELECT
COUNT (1)
FROM
(
SELECT
*
FROM
tbl_crm_SupplierInfo
WHERE
1 = 1
AND supplierType = '8F0E5D75-A8D1-43E1-85E0-0EAFA1E4212B'
AND CONVERT (
VARCHAR (100),
supplierCreateTime,
23
) LIKE concatStr ('%', '2019-07-24', '%')
) tmp_count
错误信息:
org.springframework.jdbc.UncategorizedSQLException:
### Error querying database. Cause: java.sql.SQLException: get ParameterMetaData Error:java.sql.SQLException: [KingbaseES Server]ERROR: 语法错误 在 "," 附近 Line 1 at SQL statement
### Cause: java.sql.SQLException: get ParameterMetaData Error:java.sql.SQLException: [KingbaseES Server]ERROR: 语法错误 在 "," 附近 Line 1 at SQL statement
; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; get ParameterMetaData Error:java.sql.SQLException: [KingbaseES Server]ERROR: 语法错误 在 "," 附近 Line 1 at SQL statement; nested exception is java.sql.SQLException: get ParameterMetaData Error:java.sql.SQLException: [KingbaseES Server]ERROR: 语法错误 在 "," 附近 Line 1 at SQL statement
在金仓查询分析器执行语句:1
1. SELECT
2. COUNT (1)
3. FROM
4. (
5. SELECT
6. *
7. FROM
8. tbl_crm_SupplierInfo
9. WHERE
10. 1 = 1
11. AND supplierType = '8F0E5D75-A8D1-43E1-85E0-0EAFA1E4212B'
12. AND CONVERT ( VARCHAR (100),
13. supplierCreateTime,
14. 23)
15. LIKE concatStr ('%' ,'2019-07-24', '%')
16. ) tmp_count;
执行成功: 否
执行耗时: 5 毫秒
服务器消息:
[KingbaseES Server]ERROR: 语法错误 在 "," 附近 Line 12 at SQL statement
解决办法:
编写语句时使用相应的函数替换即可(红色部分是需要修改的)。
Sqlserver写法:
SELECT
COUNT (1)
FROM
(
SELECT
*
FROM
tbl_crm_SupplierInfo
WHERE
1 = 1
AND supplierType = '8F0E5D75-A8D1-43E1-85E0-0EAFA1E4212B'
AND CONVERT ( VARCHAR (100),
supplierCreateTime,
23)
LIKE concatStr ('%' ,'2019-07-24', '%')
) tmp_count
改为:
SELECT
COUNT (1)
FROM
(
SELECT
*
FROM
tbl_crm_SupplierInfo
WHERE
1 = 1
AND supplierType = '8F0E5D75-A8D1-43E1-85E0-0EAFA1E4212B'
AND TO_DATE (supplierCreateTime,
'YYYY-MM-DD')
LIKE concatStr ('%' ,'2019-07-24', '%')
) tmp_count