oracle 时间字段类型为date 代码接受参数为string型时间戳
string型时间戳转为string型日期类型
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String time = fmt.format(Long.valueOf( startTime) * 1000 ) );
转换结果为 :2020-06-29 00:00:00
sql中解决方法
执行代码
select
*
from
PMS_LAST_APPPROVED_PROJ_INFO@ELINK
where
SUBMIT_TIME <=
'2020-06-29 00:00:00'
报错 Oracle 异常 ORA-01861: literal does not match format string(字符串格式不匹配)
如果直接按照字符串方式,或者,直接使用'2020-06-29 00:00:00',没有指定日期格式,就会报错
sql改为
select
*
from
PMS_LAST_APPPROVED_PROJ_INFO@ELINK
where
SUBMIT_TIME <=
to_date('2020-06-29 00:00:00','yyyy-MM-dd HH24:mi:ss');
执行正确;
如果数据库中存的时间格式不一样,则在查询时需先对条件格式化,使用to_char,to_date函数
select
*
from
PMS_LAST_APPPROVED_PROJ_INFO@ELINK
where
SUBMIT_TIME <=
to_date(to_char('2020-06-29 00:00:00','yyyy-MM-dd HH24:mi:ss'),'yyyy-MM-dd HH24:mi:ss');
如果传输的日期格式为 '06/29/2020 00:00:00' 会报错 ORA-01843:not a valid month(无效的月份)
select
*
from
PMS_LAST_APPPROVED_PROJ_INFO@ELINK
where
SUBMIT_TIME <=
to_date('06/29/2020','yyyy-MM-dd HH24:mi:ss');
java中解决问题
mybatis中xml写法
if test="startTime != null">
and "TO_CHAR"(SUBMIT_TIME,'YYYY-MM-DD') >= #{startTime}
</if>
<if test="endTime != null">
and "TO_CHAR"(SUBMIT_TIME,'YYYY-MM-DD') <= #{endTime}
</if>
接受参数string型日期(2020-06-29 00:00:00 )
打印出的sql语句
select *
from PMS_LAST_APPPROVED_PROJ_INFO@ELINK
where "TO_CHAR"(SUBMIT_TIME,'YYYY-MM-DD') >= '2020-06-27 00:00:00'
and "TO_CHAR"(SUBMIT_TIME,'YYYY-MM-DD') <= '2020-06-28 00:00:00';