MySQL和Oracle常用日期转换函数

最新推荐文章于 2024-08-23 17:13:59 发布

xuan323

最新推荐文章于 2024-08-23 17:13:59 发布

阅读量3.1k

点赞数

分类专栏：笔记

本文链接：https://blog.csdn.net/leozhou13/article/details/50442653

版权

笔记专栏收录该内容

11 篇文章 0 订阅

订阅专栏

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-09
select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09
select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09
select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
select 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 dual
select 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>