发布日期:2015-11-05 15:18 来源: 标签: 数据库 oracle教程 oracle函数 oracle日期函数
本章我们主要学习oracle数据库实际应用中常用日期函数函数有哪些?下面我们就做一下具体讲解,希望大家多多支持中国站长网络学院。
1、Date
1)当前日期:
CURRENT_DATE
SYSDATE 
eg:
SELECT TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM dual; 
2)格式化
————
3)可以用圆形和trunc函数格式
- One greater than the first two digits of a four-digit year
CC
SCC
- Year (rounds up on July 1)
SYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y   
- ISO Year
IYYY
IY
IY
I
- Quarter (rounds up on 16th day of the 2nd month of the quarter) 
Q
- Month (rounds up on the sixteenth day)
MONTH
MON
MM
RM
- Week of the year
WW 
- Same day of the week as the first day of the ISO year
IW
- Same day of the week as the first day of the month
W
- Day
DDD
DD
J
- Starting day of the week
DAY
DY
D
- Hours
HH
HH12
HH24
- Minute
MI

2、+ AND -
1)+:
<date> + <integer> 
eg:SELECT SYSDATE + 1 FROM dual; 
2)-:
<date> - <integer> 
eg:SELECT SYSDATE - 1 FROM dual; 

3、ADD_MONTHS:加一个月到一个日期
ADD_MONTHS(<date>, <number of months_integer> 
eg:
SELECT add_months(SYSDATE, 2) FROM dual;
-- but be aware of what it is doing
SELECT add_months(TO_DATE('27-JAN-2007'), 1) FROM dual;
SELECT add_months(TO_DATE('28-JAN-2007'), 1) FROM dual;
SELECT add_months(TO_DATE('29-JAN-2007'), 1) FROM dual;
SELECT add_months(TO_DATE('30-JAN-2007'), 1) FROM dual;
SELECT add_months(TO_DATE('31-JAN-2007'), 1) FROM dual;
SELECT add_months(TO_DATE('01-FEB-2007'), 1) FROM dual; 

4、CURRENT_DATE:在数据类型日期的阳历值返回服务器的当前日期
col sessiontimezone format a30
SELECT sessiontimezone, current_date FROM dual;
ALTER SESSION SET TIME_ZONE = '-5:0';

SELECT sessiontimezone, current_date FROM dual;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT sessiontimezone, current_date FROM dual;
ALTER SESSION SET TIME_ZONE = '-7:0';

SELECT sessiontimezone, current_date FROM dual;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

5、DUMP:返回值的数据类型的字节数和
DUMP(<value>) 
eg:SELECT DUMP(SYSDATE) FROM dual; 

6、GREATEST:返回最新的日期
LEAST(<date>, <date>, <date>, ...) 
eg:
CREATE TABLE t (
datecol1 DATE,
datecol2 DATE,
datecol3 DATE)
PCTFREE 0;

INSERT INTO t VALUES (SYSDATE+23, SYSDATE-10, SYSDATE-24);
INSERT INTO t VALUES (SYSDATE-15, SYSDATE, SYSDATE+15);
INSERT INTO t VALUES (SYSDATE-7, SYSDATE-18, SYSDATE-9);
COMMIT;

SELECT * FROM t;
SELECT GREATEST(datecol1, datecol2, datecol3) FROM t; 

7、INTERVAL:调整日期时间间隔
INTERVAL '<integer>' <unit> 
eg:
SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM dual;
SELECT TO_CHAR(SYSDATE + INTERVAL '10' MINUTE, 'HH:MI:SS') FROM dual;
SELECT TO_CHAR(SYSDATE - INTERVAL '10' MINUTE, 'HH:MI:SS') FROM dual; 

8、LAST_DAY:返回一个月的最后一个日期
LAST_DAY(<date>) 
eg:
SELECT * FROM t;
SELECT LAST_DAY(datecol1) FROM t; 

9、LEAST:返回最早的日期
LEAST(<date>, <date>, <date>, ...) 
eg:
SELECT * FROM t;
SELECT LEAST(datecol1, datecol2, datecol3) FROM t; 

10、LENGTH:字符返回长度
LENGTH(<date>) 
eg:SELECT LENGTH(last_ddl_time) FROM user_objects; 

11、LENGTHB:返回字节长度
LENGTHB(<date>) 
eg:SELECT LENGTHB(last_ddl_time) FROM user_objects; 
注:其他形式的长度(lengthC,Length2,和长度4)也可。

12、MAX:返回最新的日期
MAX(<date>) 
eg:
SELECT * FROM t;
SELECT MAX(datecol1) FROM t; 

13、MIN:返回最早的日期
MIN(<date>) 
eg:
SELECT * FROM t;
SELECT MIN(datecol1) FROM t; 

14、MONTHS_BETWEEN:返回数月分隔日期
MONTHS_BETWEEN(<latest_date>, <earliest_date>) 
eg:
SELECT MONTHS_BETWEEN(SYSDATE+365, SYSDATE-365) FROM dual;
SELECT MONTHS_BETWEEN(SYSDATE-365, SYSDATE+365) FROM dual; 

15、NEW_TIME:返回日期和时区的日期和时区区当区时间日期时间
Before using this function, you must set the NLS_DATE_FORMAT parameter to display 24-hour time. 
eg:
SELECT NEW_TIME(TO_DATE('11-10-99 01:23:45', 'MM-DD-YY HH24:MI:SS'), 'AST', 'PST') "New Date and Time" FROM dual;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT NEW_TIME(TO_DATE('11-10-99 01:23:45', 'MM-DD-YY HH24:MI:SS'), 'AST', 'PST') "New Date and Time" FROM dual;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; 

16、ROUND:传回一个数值,该数值是按照指定的小数位元数进行四舍五入运算的结果。
ROUND(<date_value>, <format>) 
eg:
SELECT ROUND(TO_DATE('27-OCT-00'),'YEAR') NEW_YEAR FROM dual; 

17、SYSDATE:返回当前会话的日期
SYSDATE 
eg:SELECT SYSDATE FROM dual; 

18、TRUNC
1)24制日期转换为日期
TRUNC(<date_time>) 
eg:
CREATE TABLE t (datecol DATE);
INSERT INTO t (datecol) VALUES (SYSDATE);
INSERT INTO t (datecol) VALUES (TRUNC(SYSDATE));
INSERT INTO t (datecol) VALUES (TRUNC(SYSDATE, 'HH'));
INSERT INTO t (datecol) VALUES (TRUNC(SYSDATE, 'MI'));
COMMIT;
SELECT TO_CHAR(datecol, 'DD-MON-YYYY HH:MI:SS') FROM t; 
2)选择性地删除日期信息的一部分
TRUNC(<date_time>, '<format>') 
eg:
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;
-- first day of the month
SELECT TO_CHAR(TRUNC(SYSDATE, 'MM'), 'DD-MON-YYYY HH:MI:SS') FROM dual;
SELECT TO_CHAR(TRUNC(SYSDATE, 'MON'), 'DD-MON-YYYY HH:MI:SS') FROM dual;
SELECT TO_CHAR(TRUNC(SYSDATE, 'MONTH'), 'DD-MON-YYYY HH:MI:SS') FROM dual;
-- first day of the year
SELECT TO_CHAR(TRUNC(SYSDATE, 'YYYY'), 'DD-MON-YYYY HH:MI:SS') FROM dual;
SELECT TO_CHAR(TRUNC(SYSDATE, 'YEAR'), 'DD-MON-YYYY HH:MI:SS') FROM dual; 
3)在子句联接的日期
SELECT SYSDATE FROM dual;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SYSDATE FROM dual;
/
/
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
CREATE TABLE t (
datecol DATE);
INSERT INTO t (datecol) VALUES (SYSDATE);
SELECT * FROM t;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT * FROM t;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
SELECT * FROM t;
SELECT SYSDATE FROM dual;
SELECT * FROM t WHERE datecol = SYSDATE;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT * FROM t;
SELECT SYSDATE FROM dual;
SELECT TRUNC(SYSDATE) FROM dual;
SELECT * FROM t WHERE TRUNC(datecol) = TRUNC(SYSDATE);
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

19、VSIZE:返回值所需的字节数
VSIZE(e IN DATE) RETURN NUMBER 
eg:SELECT VSIZE(SYSDATE) FROM dual; 

20、Date Calculations
1)在未来的周末里,一天会有一天,一个特定的天
CREATE OR REPLACE FUNCTION business_date (start_date DATE,
Days2Add NUMBER) RETURN DATE IS
 Counter  NATURAL := 0;
 CurDate  DATE := start_date;
 DayNum   POSITIVE;
 SkipCntr NATURAL := 0; 
BEGIN
  WHILE Counter < Days2Add LOOP
    CurDate := CurDate+1;
    DayNum := TO_CHAR(CurDate, 'D');

    IF DayNum BETWEEN 2 AND 6 THEN
      Counter := Counter + 1;
    ELSE
      SkipCntr := SkipCntr + 1;
    END IF;
  END LOOP;
  RETURN start_date + Counter + SkipCntr;
END business_date;
/
2)业务数据功能,以上,提高了拉里本顿为days2add参数处理负面价值。
CREATE OR REPLACE FUNCTION business_date (start_date DATE, 
days2add NUMBER) RETURN DATE IS
 Counter NATURAL := 0;
 CurDate DATE := start_date;
 DayNum POSITIVE;
 SkipCntr NATURAL := 0;
 Direction INTEGER := 1;  -- days after start_date
 BusinessDays NUMBER := Days2Add;
BEGIN
  IF Days2Add < 0 THEN
    Direction := - 1; -- days before start_date
    BusinessDays := (-1) * BusinessDays;
  END IF;

  WHILE Counter < BusinessDays LOOP
    CurDate := CurDate + Direction;
    DayNum := TO_CHAR( CurDate, 'D');

    IF DayNum BETWEEN 2 AND 6 THEN
      Counter := Counter + 1;
    ELSE
      SkipCntr := SkipCntr + 1;
    END IF;
  END LOOP;

  RETURN start_date + (Direction * (Counter + SkipCntr));
END business_date;

3)返回一个月的第一天
CREATE OR REPLACE FUNCTION fday_ofmonth(value_in DATE)
RETURN DATE IS
 vMo VARCHAR2(2);
 vYr VARCHAR2(4); 
BEGIN
  vMo := TO_CHAR(value_in, 'MM');
  vYr := TO_CHAR(value_in, 'YYYY');
  RETURN TO_DATE(vMo || '-01-' || vYr, 'MM-DD-YYYY'); 
EXCEPTION
  WHEN OTHERS THEN
    RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY'); 
END fday_ofmonth;
/


21、Time Calculations
1)返回日期时间值之间的秒数
CREATE OR REPLACE FUNCTION time_diff (
DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS


NDATE_1   NUMBER;
NDATE_2   NUMBER;
NSECOND_1 NUMBER(5,0);
NSECOND_2 NUMBER(5,0);


BEGIN
  -- Get Julian date number from first date (DATE_1)  NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));
  -- Get Julian date number from second date (DATE_2)  NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));
  -- Get seconds since midnight from first date (DATE_1) NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));
  -- Get seconds since midnight from second date (DATE_2) NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));
  RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));
END time_diff;
/
2)计算时间从秒  12 / 30 / 06 c.d.o.misc
SELECT DECODE(FLOOR(999999/86400), 0, '',
              FLOOR(999999/86400) || ' day(s), ') || 
   TO_CHAR(TO_DATE(MOD(999999, 86400),'SSSSS'), 'HH24:MI:SS') AS elapsed
FROM dual;

相关评论

专题信息
    ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。比如SilverStream就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统,作为一个通用的数据库系统,它具有完整的数据管理功能;本教程主要是针对oracle应用过程中我们常用的函数进行深入讲解,让大家能够通过代码实例更快、更有效的掌握oracle函数,希望对大家有所帮助。