Oracle日期函数

在今天之前:

select * from up_date where update < to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update <= to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')

在今天只后:

select * from up_date where update > to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update >= to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')

精确时间:

select * from up_date where update = to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')

在某段时间内:

select * from up_date where update between to_date('2007-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update < to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and update > to_date('2007-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update <= to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and update >= to_date('2007-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss')

我在项目开发中用到的实际例子：

SELECT * FROM TERMINAL_ORDER_HEAD
where LAST_UPDATED_TIME
between to_date(to_char(SYSDATE,'yyyyMMdd'),'yyyyMMdd')
AND to_date(to_char(SYSDATE,'yyyyMMdd'),'yyyyMMdd')+0.99999;

to_date(to_char(SYSDATE,'yyyyMMdd'),'yyyyMMdd')+18/24 当天的16:00

to_date(to_char(SYSDATE,'yyyyMMdd'),'yyyyMMdd')+24/24 明天的00:00

（注意：一定要是5位9，不信你少打两个9试试。呵呵，就不是59分59秒了。）

实例：

public int Select_JinRiFw(int userid)//查询会员今日访问量
{
string sql = string.Format("select count(*) from mis.YXJC_TWEB_INFOVISITS where USER_ID={0} and VISITS_TIME>=to_date(to_char(SYSDATE,'yyyy-MM-dd'),'yyyy-MM-dd') and VISITS_TIME<=to_date(to_char(SYSDATE,'yyyy-MM-dd'),'yyyy-MM-dd')+0.99999", userid);
DataTable dt = DBHelper.GetDataSet(sql);
return Convert.ToInt32(dt.Rows[0][0].ToString());
}

public int Select_ZuoRiFw(int userid)//查询会员昨天访问量
{
string sql = string.Format("select count(*) from mis.YXJC_TWEB_INFOVISITS where USER_ID={0} and VISITS_TIME>=to_date(to_char(SYSDATE-1,'yyyy-MM-dd'),'yyyy-MM-dd') and VISITS_TIME<=to_date(to_char(SYSDATE-1,'yyyy-MM-dd'),'yyyy-MM-dd')+0.99999", userid);
DataTable dt = DBHelper.GetDataSet(sql);
return Convert.ToInt32(dt.Rows[0][0].ToString());
}

一、日期算法

1、sysdate、current_date及systimestamp

Oracle使用计算机操作系统的当前日期和时间，是通过sysdate函数实现的,在任何可以使用Oracle函数的其它地方都可以使用sysdate函数，可以将它视为每个表的一个隐藏列或者伪列。

select sysdate from dual;    

 

结果：2013/12/22 19:14:21

current_date 返回的是当前会话的时区的系统日期，我们平时用的都是东八区，如果将当前会话时间修改成东九区试试：

alter session set time_zone= '+09:00' ;

 

select current_date ,sysdate  from  dual

 

结果： 2013/12/24 20:27:33 2013/12/24 19:27:32

通过例子我们也看到它俩的区别了。

还有一个函数systimestapm，它返回的是本机的系统时间（包含微秒和时区），它跟会话的时区无关

select  systimestamp  from dual

 

结果： 24-12月-13 07.34.37.042000 下午 +08:00

2、两个日期的差

两个日期可以直接进行加减，返回的数值单位是天，也可以用一个日期加减某个数值代表N天后(前)

比如十天后发工资，那么十天后是那一天呢?

select  sysdate+10  from dual

 

结果： 2014/1/1 19:38:19

再比如今天除夕是2014-01-30，那么距离过年还有哪个多少天呢

select to_date( '2014-01-30' , 'yyyy-MM-dd' )  -sysdate  from dual

 

结果： 38.1794097222222

晕，还有一个多月才能过年呢啊

3、添加月份、减少月份

用的都是add_months

查询一下三个月后、三个月前分别是哪一天：

select add_months(sysdate,3),add_months(sysdate,-3)   from  dual

 

结果： 2014/3/22 19:47:18  2013/9/22 19:47:18

添加年份、减少年份就不用说了，直接在数值上乘以12就可以推算N年前、N年后是哪一天

4、greatest和least

这两个函数在介绍数值函数的时候提到过，在数值函数中分别代表取一组数值中的最大值和最小值。

这哥俩在日期函数中也有应用，greatest是从一组日期数据中取距离当前最近的日期，least是在一组日期中选择最早的日期。

select  greatest(to_date( '2013-11-11' , 'yyyy-MM-dd' ),to_date( '2013-12-11' , 'yyyy-MM-dd' ),to_date( '2014-01-05' , 'yyyy-MM-dd' ),to_date( '2013-11-11' , 'yyyy-MM-dd' ))  from  dual

 

结果：1    2013/1/5

 

select  least(to_date( '2013-11-11' , 'yyyy-MM-dd' ),to_date( '2013-12-11' , 'yyyy-MM-dd' ),to_date( '2014-01-05' , 'yyyy-MM-dd' ),to_date( '2013-11-11' , 'yyyy-MM-dd' ))  from  dual

 

结果：1    2013/11/11

注意：这里必须用to_date对字符串进行转化，否则会把他们无法判断是否为日期格式，只能当成字符串去处理。容易出现结果错误的情况。

5、next_day 代表从指定日期开始算，下周几是哪一天

select next_day(to_date( '2013-12-23' , 'yyyy-MM-dd' ),2)  from  dual

 

结果：2013/12/30

需要注意的是：这里的2代表的是周一，而不是周二。 1234567 分别代表 周日、周一......周六。

6、last_day 计算某个月的最后一天是哪天?

select  last_day(sysdate) from dual

 结果： 2013/12/31 20:46:07

 

7、months_between 计算两个日期之间相差的月份数，返回结果可能带小数




select  last_day(sysdate) from dual

 结果： 2013/12/31 20:46:07

计算下自己的周岁：

select floor(months_between(sysdate ,to_date( '1990-08-24' , 'yyyy-MM-dd' ))/12)  from dual 

结果：23

8、组合日期函数 ： 多个日期函数组合使用

举例：2013年10月28日入职新公司，俩月试用期，新员工过了试用期后的每月第一天可以做转正报告。 那10月28日入职的什么时候做转正报告：

select   last_day(add_months(to_date( '2013-10-28' , 'yyyy-MM-dd' ),2))+1  from dual

 结果： 2014/1/1

思路：先用add_months计算俩月后是哪一天，再用last_day计算那个月的最后一天，再+1代表下个月的第一天。

9、日期计算中的round 和 trunc

在数值函数一文中也着重介绍了round和trunc两个函数，那块介绍的是他们在数值函数方面的应用。其实在日期函数中这兄弟俩跟数值函数中的作用类似。

我们用一个日期减去另一个日期的时候经常会带有小数，这是因为Oracle中保留了时、分、秒。

有的时候我们不需要时、分、秒，所以就需要用到round和trunc这两个函数了。

round函数作用是：如果某天的时间在中午之前，那么使用round函数会将改天的时间设置为0点（就是午夜），如果为中午之后就设置为次日零点，这也就是时间方面的四舍五入了。

trunc的函数作用：舍去，无论某天处于什么时间，使用trunc函数后都会将改天时间置为当日0点。

select to_date( '2013-12-23' , 'yyyy-MM-dd' )-round(sysdate), to_date( '2013-12-23' , 'yyyy-MM-dd' )-trunc(sysdate) from dual

 结果：0  

二、使用to_date 和 to_char设置日期时间格式

1、基本使用

to_date 和 to_char两个函数的作用是相反的，但是用法很类似

to_date是将字符串转换为时间格式 to_char是将日期转换为字符串格式

这两个函数我们平时用的实在是太多了，先写俩例子吧，然后再说其他的：

select to_date( '2013-11-11 12:01:14' , 'yyyy-MM-dd HH:MI:SS' )  FROM  DUAL

 

结果： 2013/11/11 12:01:14

 

select to_char(sysdate , 'yyyy-MM-dd HH:MI:SS' )  from  dual;

 

结果： 2013-12-22 09:37:48

to_char的时候可以在format参数中插入字符串，以双引号的方式插入：

select to_char(sysdate , 'yyyy"年"MM"月"dd"日"' )  from  dual;

 结果： 2013年12月22日

2、最常见的to_char错误

就是MM代表的是月份 MI代表队 是分钟 不要弄混了，由于Oracle中支持的时间很灵活，所以这块弄混了他不会提示你的。

select  to_char(sysdate , 'yyyy-MI-dd  hh:mm:ss' )  from  dual

 

结果： 2013-45-22  09:12:15

出现这样的错误就很悲催了，好在分钟是45还能容易发现， 要是1-12之内的，要从结果发现错误也不容易。

3、to_date 不仅可以接受字符串的参数，还可以接受数值类型的参数：

select  to_date(20131222, 'yyyy-MM-dd' ) from dual

 

结果： 2013/12/22

4、new_time 切换时区

new_time(date,'this','other')

三个参数：date代表的是this时区的时间，‘this’代表的是是用三个字母组成的缩写代替，代表当前时区，‘other’也是用三个字母组成的缩写代替，表示其他时区。

举例：查一下‘2014-10-01 12:00:00’这个时间在北京和夏威夷的时间分别是多少

select  to_date( '2014-10-01 12:00:00' , 'yyyy-MM-dd hh24:mi:ss' ),new_time(to_date( '2014-10-01 12:00:00' , 'yyyy-MM-dd hh24:mi:ss' ), 'EST' , 'HST' )  FROM DUAL

 

结果： 2014/10/1 12:00:00  2014/10/1 7:00:00

应该不会常用，了解下就行了。

三、EXTRACT

EXTRACT可用来代替to_char函数来选择日期值中的某一部分（如从一个日期中选择月份和天）

SELECT EXTRACT( YEAR FROM SYSDATE) FROM DUAL;

SELECT EXTRACT( MONTH FROM SYSDATE) FROM DUAL;

SELECT EXTRACT( DAY FROM SYSDATE) FROM DUAL;

 

结果：

2013

12

22

注意：EXTRACT抽取年月日部分和抽取时分部分是不同的，抽取时分的时候

SELECT EXTRACT( hour FROM TIMESTAMP  '2013-12-22 22:34:12' ) FROM DUAL;

SELECT EXTRACT( minute FROM TIMESTAMP  '2013-12-22 22:34:12' ) FROM DUAL;

 

结果：

22

34

而且如果将'2013-12-22 22:34:12'用to_date转换成日期格式还报错，真让我想不通

再说了这个函数的存在有毛用呢？至今没想明白，用to_char完全可以实现它的功能啊

select to_char(sysdate , 'yyyy' )  from dual;

select to_char(sysdate , 'HH' )  from dual

 

结果：2013

10

四、使用TIMESTAMP类型

DATE 在数据类型存储精确到秒的日期和时间，二TIMESTAMP数据类型存储精确到十亿分之一秒的日期。