ORA-01861: 文字与格式字符串不匹配
我写个报表sql：如下
SELECT DECODE(ROW_NUMBER() OVER(PARTITION BY BUREAUNAME ORDER BY A.RN2,
A.DEFECTLEVELCODE DESC),
1,
4,
0) ROWSPAN,
DECODE(A.BUREAUNAME, 'NULL', '姹囨?', A.BUREAUNAME) BUREAU_NAME,
A.*,
NVL(B.TOTALFINDINTHISMONTH, 0) TOTALFINDINTHISMONTH,
NVL(B.DEALEDFINDINTHISMONTH, 0) DEALEDFINDINTHISMONTH,
NVL(B.DEALEDINTIMEFINDINTHISMONTH, 0) DEALEDINTIMEFINDINTHISMONTH,
NVL(B.LEGACYTOTAL, 0) LEGACYTOTAL,
NVL(B.LEGACYDEALINTHISMONTH, 0) LEGACYDEALINTHISMONTH,
NVL(B.LEGACYDEALEDINTIMEINTHISMONTH, 0) LEGACYDEALEDINTIMEINTHISMONTH
FROM (SELECT NVL(A.BUREAUNAME, 'NULL') BUREAUNAME,
NVL(B.DEFECTLEVELCODE, 0) DEFECTLEVELCODE,
MAX(A.BCODE) BUREAU_CODE,
MAX(B.DEFECTDAMAGELEVEL) DEFECTLEVELNAME,
GROUPING(A.BUREAUNAME) RN1,
GROUPING(B.DEFECTLEVELCODE) RN2
FROM (SELECT DISTINCT BUREAU_CODE BCODE, BUREAU_NAME BUREAUNAME
FROM WO_DEFECT
WHERE BUREAU_CODE = '03'
UNION ALL
SELECT DISTINCT BUREAU_CODE BCODE, BUREAU_NAME BUREAUNAME
FROM WO_DEFECT
WHERE BUREAU_CODE = '0351'
UNION ALL
SELECT DISTINCT BUREAU_CODE BCODE, BUREAU_NAME BUREAUNAME
FROM WO_DEFECT
WHERE BUREAU_CODE = '0306'
UNION ALL
SELECT DISTINCT BUREAU_CODE BCODE, BUREAU_NAME BUREAUNAME
FROM WO_DEFECT
WHERE BUREAU_CODE = '0303') A,
(SELECT '绱ф?' DEFECTDAMAGELEVEL, 1 DEFECTLEVELCODE
FROM DUAL
UNION ALL
SELECT '閲嶅ぇ', 2
FROM DUAL
UNION ALL
SELECT '涓?埇', 3 FROM DUAL) B
GROUP BY CUBE(A.BUREAUNAME, B.DEFECTLEVELCODE)) A
LEFT JOIN (SELECT NVL(T.BUREAU_NAME, 'NULL') BUREAU_NAME,
MAX(T.BUREAU_CODE),
NVL(T.DEFECT_LEVEL, 0) DEFECT_LEVEL,
COUNT(1) RN,
GROUPING(T.DEFECT_LEVEL) RN2,
GROUPING(T.BUREAU_NAME) RN1,
COUNT((CASE
WHEN T.FIND_TIME BETWEEN
TO_DATE(?, 'yyyy-MM-dd') AND
TO_DATE(?, 'yyyy-MM-dd') AND
T.STATE != 1 THEN
1
END)) TOTALFINDINTHISMONTH,
COUNT((CASE
WHEN T.FIND_TIME BETWEEN
TO_DATE(?, 'yyyy-MM-dd') AND
TO_DATE(?, 'yyyy-MM-dd') AND
T.DEAL_TIME BETWEEN
TO_DATE(?, 'yyyy-MM-dd') AND
TO_DATE(?, 'yyyy-MM-dd') AND
T.STATE != 1 THEN
1
END)) DEALEDFINDINTHISMONTH,
COUNT((CASE
WHEN T.FIND_TIME BETWEEN
TO_DATE(?, 'yyyy-MM-dd') AND
TO_DATE(?, 'yyyy-MM-dd') AND
T.DEAL_TIME BETWEEN
TO_DATE(?, 'yyyy-MM-dd') AND
TO_DATE(?, 'yyyy-MM-dd') AND
T.OUGHT_DEAL_TIME > T.DEAL_TIME AND T.STATE != 1 THEN
1
END)) DEALEDINTIMEFINDINTHISMONTH,
COUNT((CASE
WHEN TO_DATE(?, 'yyyy-MM-dd ') > T.FIND_TIME AND
T.DEAL_TIME IS NULL OR
(T.DEAL_TIME IS NOT NULL AND
T.DEAL_TIME > TO_DATE(?, 'yyyy-MM-dd')) AND
T.STATE != 1 THEN
1
END)) LEGACYTOTAL,
COUNT((CASE
WHEN TO_DATE(?, 'yyyy-MM-dd') > T.FIND_TIME AND
T.DEAL_TIME BETWEEN
TO_DATE(?, 'yyyy-MM-dd') AND
TO_DATE(?, 'yyyy-MM-dd') AND
T.STATE != 1 THEN
1
END)) LEGACYDEALINTHISMONTH,
COUNT((CASE
WHEN TO_DATE(?, 'yyyy-MM-dd') > T.FIND_TIME AND
T.DEAL_TIME BETWEEN
TO_DATE(?, 'yyyy-MM-dd') AND
TO_DATE(?, 'yyyy-MM-dd') AND
T.OUGHT_DEAL_TIME > T.DEAL_TIME AND T.STATE != 1 THEN
1
END)) LEGACYDEALEDINTIMEINTHISMONTH
FROM WO_DEFECT T
WHERE T.DEFECT_LEVEL IN (1, 2, 3)
AND T.BUREAU_NAME IS NOT NULL
GROUP BY CUBE(T.BUREAU_NAME, T.DEFECT_LEVEL)) B ON A.BUREAUNAME =
B.BUREAU_NAME
AND A.DEFECTLEVELCODE =
B.DEFECT_LEVEL
ORDER BY A.RN1, A.BUREAU_CODE, A.RN2, A.DEFECTLEVELCODE DESC
传入的参数分别是：2011-12-01 14:44:05.212(Timestamp), 2011-12-31 14:44:05.212(Timestamp), 2011-12-01 14:44:05.212(Timestamp), 2011-12-31 14:44:05.212(Timestamp), 2011-12-01 14:44:05.212(Timestamp), 2011-12-31 14:44:05.212(Timestamp), 2011-12-01 14:44:05.212(Timestamp), 2011-12-31 14:44:05.212(Timestamp), 2011-12-01 14:44:05.212(Timestamp), 2011-12-31 14:44:05.212(Timestamp), 2011-12-01 14:44:05.212(Timestamp), 2011-12-01 14:44:05.212(Timestamp), 2011-12-01 14:44:05.212(Timestamp), 2011-12-01 14:44:05.212(Timestamp), 2011-12-31 14:44:05.212(Timestamp), 2011-12-01 14:44:05.212(Timestamp), 2011-12-01 14:44:05.212(Timestamp), 2011-12-31 14:44:05.212(Timestamp)。
运行报错：ORA-01861: 文字与格式字符串不匹配。
--------------------编程问答-------------------- TO_DATE(?, 'yyyy-MM-dd')
传的参数应该是字符串型，格式2011-12-01 --------------------编程问答--------------------
此兄说的有道理，如果是Date类型，可以去掉TO_DATE也可。
分给你了！多谢
补充：Java , Web 开发
