MySQL和Oracle常用日期转换函数

MySQL Date/Time to Str（日期/时间转换为字符串）函数：date_format(date,format), time_format(time,format)/*日期转字符串*/select date_format(JZKSRQ,'%Y-%c-%d %H:%i:%s') 日期 from healthtest.mb_medicalrecord where JZLSH = '20151221003';select date_format(JZKSRQ,'%Y-%c-%d') 日期 from healthtest.mb_medicalrecord where JZLSH = '20151221003';select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');select date_format('2013-11-11 22:23:01','%Y-%c-%d %H:%i:%s');select date_format('2013-11-11 22:23:01','%Y-%c-%d %h:%i:%s');select date_format('2013-11-11','%Y-%c-%d %H:%i:%s');select date_format('2013-11-11','%Y-%c-%d');MySQL Str to Date （字符串转换为日期）函数：str_to_date(str, format)select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30 /*字符串日期*/select str_to_date('2012-11-13','%Y-%c-%d')日期;select str_to_date('2012-11-13 22:12:13','%Y-%c-%d %H:%i:%s')日期;select str_to_date('2012/11/14','%Y/%c/%d');Oracle日期转字符串：select * from emp where to_char(hiredate,'yyyy-mm-dd')>'1992-4-7'select  create_date from tb_employee t where to_char(create_date,'yyyy-MM-dd')>'2015-06-01'select  create_date from tb_employee t where to_char(create_date,'yyyy-MM-dd HH:mm:ss')>'2015-06-01 18:55:00'Oracle字符串转日期select to_date('2005-10-02,13:25:59','yyyy-mm-dd,hh24:mi:ss') from dualselect to_date('2005-10-02','yyyy-mm-dd') from dual

附：一段oralce数据库在Mybatis中xml的代码：

<select id="getPageFilePo" parameterType="page" resultType="com.linkonworks.df.vo.FilePo">select  FILEID "fileid", FILENAME "filename", ADDRESSURL "addressurl", JOBNUM "jobnum", CREATEDATE "createdate", UNIT_CODE "part", STATE "state", STATE "operation" from lkpspf.tb_file where 1=1       <if test=" params.filename != null and params.filename != ''">and FILENAME like '%${params.filename}%'   </if>   <if test=" params.flupDateStart1 != null and params.flupDateStart1 != ''"><!-- and CREATEDATE>=#{params.flupDateStart1} -->and to_char(CREATEDATE,'yyyy-MM-dd')>=#{params.flupDateStart1}   </if>   <if test=" params.flupDateEnd1 != null and params.flupDateEnd1 != ''"><!-- and CREATEDATE<=#{params.flupDateEnd1} -->and to_char(CREATEDATE,'yyyy-MM-dd')<=#{params.flupDateEnd1}   </if>   <if test=" params.unitCode != null and params.unitCode != ''"><!-- and UNIT_CODE=#{params.unitCode} -->and UNIT_CODE in (#{params.unitCode},'3')   </if>   <if test=" params.jobnum != null and params.jobnum != ''">and JOBNUM=#{params.jobnum}   </if>   <if test=" params.state != null and params.state != ''">and state=#{params.state}   </if>   ORDER BY CREATEDATE DESC</select>