十五年测试老手，长期负责WEB\APP 项目测试，目前主要负责团队管理工作。
【转】Oracle时间运算
上一篇 / 下一篇 2011-07-09 15:04:27 / 个人分类：Oracle
目录
=========================================
1.Oracle的日期函数
2.日期加减
3.月份加减
4.年份加减
5.求每月的最后一天
6.求每月的第一天
7.求下一个星期几
入门知识：
①Oracle中的日期时间存储：
oracle数据库中存放时间格式的数据，是以oracle特定的格式存贮的，占7个字节，与查询时显示的时间格式无关。不存贮秒以下的时间单位。
②Oracle中的日期时间显示：
通常，客户端与数据库建立起连接后，oracle就会给一个缺省的时间格式数据的显示形式，与所使用的字符集有关。一般显示年月日，而不显示时分秒。
③Oracle中的日期时间插入：
向表中插入数据时，如果不使用转换函数，则时间字段的格式必须遵从会话环境的时间格式，否则不能插入。
④Oracle中的日期时间格式修改：
a.SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
b.册表\hkey_local_machine\software\oracle\home0主键中增加一个字串(8i版本)，字串名为nls_date_format，字串的值为你希望定义的时间格式
前者只对当前会话有效，也即是一旦你关闭了SQL*PLUS窗口或重新打开一个SQL*PLUS窗口，日期时间格式依然采用本地字符集对应的日期时间格式。后者对所有客户端应用有效。当两者同时应用时，以alter session的修改为准。
一、Oracle的日期函数:
Oracle从8i开始就提供了大量的日期函数，这些日期函数包括对日期进行加减、转换、截取等功能。下面是Oracle提供的日期函数一览表
二、日期加减：
在Oralce中，对日期进行加减操作的默认单位是天，也就是说如果我们向当前日期加1的话是加上一天，而不是一秒或一小时。那么对一天中的一段时间进行加减要怎么做呢？很简单！只需将它们转化为以天为单位即可。
【1】为当前时间加上30分钟：
SQL> to_char(sysdate, 'yyyy-mm-dd hh:mi:ss ') now_date,
2 to_char(sysdate+(30/24/60), hh:mi:ss ') new_date
3 from dual;
NOW_DATE NEW_DATE
-------------------------------------- --------------------------------------
2008-06-30 2008-06-30
SQL>
我们看到了在绿色高亮处使用30/24/60将分钟转换成天。另外一个要注意的地方是：SQL*PLUS环境下默认的日期格式：NLS_DATE_FORMAT是DD-MM-YYYY，也即是不包含时、分、秒，所以我们这里必须采用to_char的方式指定输入的日期格式。
除此之外也可以通过在SQL*PLUS中执行下列语句修改默认的日期输出格式，这样的话就不需要通过to_char来转换了，直接输出就行。alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss ';
【2】为当前时间减去30分钟：SQL> to_char(sysdate +(-30/24/60),'yyyy-mm-dd hh:mi:ss ') new_date from dual;
NEW_DATE
--------------------------------------
2008-06-30
只需要加上一个负数即可以了。
三、月份加减：
月份的加减和日期加减相比要难了很多，因为每个月份的天数并不是固定的，可能是31,30,29,28。如果采用上面的方法将月份转换成实际天数将不可避免地出现多个判断，幸亏Oracle为我们提供了一个add_months函数，这个函数会自动判断月份的天数。看看下面的例子：
【1】为当前时间加上6个月：SQL> add_months(sysdate, 6) dual;
ADD_MONTHS
----------
31-12月-08
【2】为当前时间减去6个月：SQL> add_months(sysdate, -6) dual;
ADD_MONTHS
----------
31-12月-07
【3】求两个日期相差的月数：
通常情况下两个时间相减将得到以天数为单位的结果，可是有时我们更希望得到以月为单位的结果，如果手动转换这太麻烦了，所以Oracle又提供了一个函数，这个函数就是months_between。SQL> months_between(sysdate,
2 to_date( '2008-01-01 01:00:00 ', hh:mi:ss ')) result
3 from dual;
RESULT
----------
5.94928203
months_between函数有2个参数，第一个参数是结束日期，第二个参数是开始日期，Oracle用第一个参数减去第二个参数得到月份数。所以结果有可能会是负数的。
四、年份加减：
Oracle并不直接提供对年份进行加减的函数，不过有了add_months和months_between函数，我们照样可以做到。
【1】为当前日期加上2年：SQL> add_months(sysdate, two_years_later
2 from dual;
TWO_YEARS_
----------
=========================================
1.Oracle的日期函数
2.日期加减
3.月份加减
4.年份加减
5.求每月的最后一天
6.求每月的第一天
7.求下一个星期几
入门知识：
①Oracle中的日期时间存储：
oracle数据库中存放时间格式的数据，是以oracle特定的格式存贮的，占7个字节，与查询时显示的时间格式无关。不存贮秒以下的时间单位。
②Oracle中的日期时间显示：
通常，客户端与数据库建立起连接后，oracle就会给一个缺省的时间格式数据的显示形式，与所使用的字符集有关。一般显示年月日，而不显示时分秒。
③Oracle中的日期时间插入：
向表中插入数据时，如果不使用转换函数，则时间字段的格式必须遵从会话环境的时间格式，否则不能插入。
④Oracle中的日期时间格式修改：
a.SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
b.册表\hkey_local_machine\software\oracle\home0主键中增加一个字串(8i版本)，字串名为nls_date_format，字串的值为你希望定义的时间格式
前者只对当前会话有效，也即是一旦你关闭了SQL*PLUS窗口或重新打开一个SQL*PLUS窗口，日期时间格式依然采用本地字符集对应的日期时间格式。后者对所有客户端应用有效。当两者同时应用时，以alter session的修改为准。
一、Oracle的日期函数:
Oracle从8i开始就提供了大量的日期函数，这些日期函数包括对日期进行加减、转换、截取等功能。下面是Oracle提供的日期函数一览表
Function | Use |
ADD_MONTHS | Adds months to a date |
LAST_DAY | Computes the last day of the month |
MONTHS_BETWEEN | Determines the number of months between two dates |
NEW_TIME | Translates a time to a new time zone |
NEXT_DAY | Returns the date of the next specified weekday |
ROUND | Rounds a date/time value to a specified element |
SYSDATE | Returns the current date and time |
TO_CHAR | Converts dates to strings |
TO_DATE | Converts strings and numbers to dates |
TRUNC | Truncates a date/time value to a specific element |
二、日期加减：
在Oralce中，对日期进行加减操作的默认单位是天，也就是说如果我们向当前日期加1的话是加上一天，而不是一秒或一小时。那么对一天中的一段时间进行加减要怎么做呢？很简单！只需将它们转化为以天为单位即可。
【1】为当前时间加上30分钟：
SQL> NOW_DATE 2008-06-30 SQL> 我们看到了在绿色高亮处使用30/24/60将分钟转换成天。另外一个要注意的地方是：SQL*PLUS环境下默认的日期格式：NLS_DATE_FORMAT是DD-MM-YYYY，也即是不包含时、分、秒，所以我们这里必须采用to_char的方式指定输入的日期格式。
除此之外也可以通过在SQL*PLUS中执行下列语句修改默认的日期输出格式，这样的话就不需要通过to_char来转换了，直接输出就行。
alter 【2】为当前时间减去30分钟：
SQL> NEW_DATE2008-06-30 只需要加上一个负数即可以了。
三、月份加减：
月份的加减和日期加减相比要难了很多，因为每个月份的天数并不是固定的，可能是31,30,29,28。如果采用上面的方法将月份转换成实际天数将不可避免地出现多个判断，幸亏Oracle为我们提供了一个add_months函数，这个函数会自动判断月份的天数。看看下面的例子：
【1】为当前时间加上6个月：
SQL> ADD_MONTHS31-12月-08【2】为当前时间减去6个月：
SQL> ADD_MONTHS31-12月-07【3】求两个日期相差的月数：
通常情况下两个时间相减将得到以天数为单位的结果，可是有时我们更希望得到以月为单位的结果，如果手动转换这太麻烦了，所以Oracle又提供了一个函数，这个函数就是months_between。
SQL> 5.94928203months_between函数有2个参数，第一个参数是结束日期，第二个参数是开始日期，Oracle用第一个参数减去第二个参数得到月份数。所以结果有可能会是负数的。
四、年份加减：
Oracle并不直接提供对年份进行加减的函数，不过有了add_months和months_between函数，我们照样可以做到。
【1】为当前日期加上2年：
SQL> TWO_YEARS_相关阅读:
- ORA-03113 (yubiao584521, 2011-7-05)
- ORA-12547 (yubiao584521, 2011-7-05)
- ora-28001 (yubiao584521, 2011-7-05)
- ORA-01034 (yubiao584521, 2011-7-05)
- 【转】自动工作负荷库(译) (msnshow, 2011-7-09)
我的栏目
标题搜索
日历
|
|||||||||
| 日 | 一 | 二 | 三 | 四 | 五 | 六 | |||
| 1 | 2 | 3 | 4 | 5 | 6 | ||||
| 7 | 8 | 9 | 10 | 11 | 12 | 13 | |||
| 14 | 15 | 16 | 17 | 18 | 19 | 20 | |||
| 21 | 22 | 23 | 24 | 25 | 26 | 27 | |||
| 28 | 29 | 30 | |||||||
我的存档
数据统计
- 访问量: 1693975
- 日志数: 556
- 图片数: 1
- 文件数: 3
- 书签数: 8
- 建立时间: 2007-06-08
- 更新时间: 2023-01-07
