Oracle sql 错误 ： ORA-01861: 文字与格式字符串不匹配和日期与字符串互转问题解决

<update id="update" parameterType="String">

update table set ceratedate=to_date(#{ceratedate}，‘yyyy-mm-dd‘);

</update>

运行 OK 没有问题

那我们查询时，如何将时间格式转换成字符串呢？要用到to_char()函数

Date格式转换成String格式

select to_char(a.createdate,‘yyyy-mm-dd‘) from table a

update  表名 set  字段 = to_date(‘2016/11/10 14:50:15‘,‘yyyy/mm/dd hh24:mi:ss‘);

原因 ： ORA-01861: 文字与格式字符串不匹配。这是在处理Oracle的时间字段时，经常会出的一个错误。

网上找不到解决的好方法，其实很简单，在SQL语句中用了TO_CHAR了，那么OracleType就不能是DateTime了，而应该是Varchar。即，

<span style="text-decoration: underline;"><span class="pln">command<span class="pun">.<span class="typ">Parameters<span class="pun">.<span class="typ">Add<span class="pun">(<span class="str">"<span class="str">DATE_PARAM<span class="str">"<span class="pun">,<span class="pln"> <span class="typ">OracleType<span class="pun">。<span class="typ">VarChar<span class="pun">).<span class="typ">Value<span class="pln"> <span class="pun">=<span class="pln"> <span class="typ">DateTime<span class="pun">.<span class="typ">Now<span class="pun">.<span class="typ">ToShortDateString<span class="pun">();<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

<span style="text-decoration: underline;"><span class="pln"><span class="pun"><span class="typ"><span class="pun"><span class="typ"><span class="pun"><span class="str"><span class="str"><span class="str"><span class="pun"><span class="pln"><span class="typ"><span class="pun"><span class="typ"><span class="pun"><span class="typ"><span class="pln"><span class="pun"><span class="pln"><span class="typ"><span class="pun"><span class="typ"><span class="pun"><span class="typ"><span class="pun"> </span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

Oracle sql 错误 ： ORA-01861: 文字与格式字符串不匹配和日期与字符串互转问题解决

标签：lang   short   查询   ext   val   cer   匹配   转换   简单