hsqldb - 使用date型態時一直出現錯誤java.sql.SQLDataException: data exception: invalid datetime format: yyyyMMdd...

參考：Chapter 10. Built In Functions

使用hsqldb+mybatis執行以下的sql cmd：

UPDATE TABLE      SET TRANS_NET_DIS=0,TRANS_SALES=0,TRANS_ADJ_DIS=0,TRANS_FEE=0,TRANS_DIS=0,TRANS_NET_SALES=0,TRANS_ADJ_SALES=0,TRANS_RECEIPT=0       WHERE STMT_DATE=to_date('20121230','yyyyMMdd') AND SEQE='00' AND MERCH='999160691' AND ORGG='822'

然後出現以下的錯誤：

### Cause: java.sql.SQLDataException: data exception: invalid datetime format: yyyyMMdd

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:150)

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:49)

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)

at com.sun.proxy.$Proxy6.updateIsKeyData(Unknown Source)

at com.ctbc.transfile.sql.mybatis.UpdateIsKeyData.execute(UpdateIsKeyData.java:21)

at com.ctbc.transfile.sql.mybatis.UpdateIsKeyData.execute(UpdateIsKeyData.java:1)

at com.ctbc.util.sql.MyBatisUtil.execute(MyBatisUtil.java:42)

at com.ctbc.transfile.loadtask.check.KeyChecker.rmvAndUpdateKeyExistsBeans(KeyChecker.java:89)

at ut.com.ctbc.transfile.loadtask.check.KeyCheckerTest.testRmvKeyExistsBeans_Exists(KeyCheckerTest.java:211)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)

at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)

at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)

at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)

at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)

at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)

at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)

at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)

at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)

at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)

at org.junit.runners.ParentRunner.run(ParentRunner.java:309)

at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)

at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

Caused by: java.sql.SQLDataException: data exception: invalid datetime format: yyyyMMdd

at org.hsqldb.jdbc.Util.sqlException(Unknown Source)

at org.hsqldb.jdbc.Util.sqlException(Unknown Source)

at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)

at org.hsqldb.jdbc.JDBCPreparedStatement.execute(Unknown Source)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:55)

at com.sun.proxy.$Proxy8.execute(Unknown Source)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)

at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)

at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100)

at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)

... 33 more

Caused by: org.hsqldb.HsqlException: data exception: invalid datetime format: yyyyMMdd

at org.hsqldb.error.Error.error(Unknown Source)

at org.hsqldb.error.Error.error(Unknown Source)

at org.hsqldb.HsqlDateTime.toJavaDatePattern(Unknown Source)

at org.hsqldb.HsqlDateTime.toDate(Unknown Source)

at org.hsqldb.FunctionCustom.getValue(Unknown Source)

at org.hsqldb.FunctionSQL.getValue(Unknown Source)

at org.hsqldb.RangeVariable$RangeIteratorMain.getFirstRow(Unknown Source)

at org.hsqldb.RangeVariable$RangeIteratorMain.initialiseIterator(Unknown Source)

at org.hsqldb.RangeVariable$RangeIteratorMain.next(Unknown Source)

at org.hsqldb.StatementDML.executeUpdateStatement(Unknown Source)

at org.hsqldb.StatementDML.getResult(Unknown Source)

at org.hsqldb.StatementDMQL.execute(Unknown Source)

at org.hsqldb.Session.executeCompiledStatement(Unknown Source)

at org.hsqldb.Session.execute(Unknown Source)

... 47 more

後來找到hsqldb的官方說明，直接找到下方的Table 10.1. TO_CHAR, TO_DATE and TO_TIMESTAMP format elements部分，可以看到允許的都是『大寫』，因此將上述執行的yyyyMMdd改為YYYYMMDD，就可以解決這個問題了．