<pre name="code" class="sql">ORACLE日期时间函数大全
TO_DATE格式(以时间:2016-11-02 15:25:36为例)
YEAR:YY TWO DIGITS 两位年 显示值:16YYY THREE DIGITS 三位年 显示值:016YYYY FOUR DIGITS 四位年 显示值:2016
MONTH:
MM NUMBER 两位月 显示值:11
MON ABBREVIATED 字符集表示 显示值:11月,若是英文版,显示NOV
MONTH SPELLED OUT 字符集表示 显示值:11月,若是英文版,显示NOVEMBER
DAY:
DD NUMBER 当月第几天 显示值:02
DDD NUMBER 当年第几天 显示值:02
DY ABBREVIATED 当周第几天简写 显示值:星期五,若是英文版,显示FRI
DAY SPELLED OUT 当周第几天全写 显示值:星期五,若是英文版,显示FRIDAY
DDSPTH SPELLED OUT, ORDINAL TWELFTH
HOUR:MINUTE:SECOND:
HH TWO DIGITS 12小时进制 显示值:01
HH24 TWO DIGITS 24小时进制 显示值:13
MI TWO DIGITS 60进制 显示值:45
SS TWO DIGITS 60进制 显示值:25
其它
Q DIGIT 季度 显示值:4
WW DIGIT 当年第几周 显示值:44
W DIGIT 当月第几周 显示值:1
24小时格式下时间范围为: 0:00:00 - 23:59:59....
12小时格式下时间范围为: 1:00:00 - 12:59:59 ....
1. 日期和字符转换函数用法(TO_DATE,TO_CHAR)
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS NOWTIME FROM DUAL; //日期转化为字符串
SELECT TO_CHAR(SYSDATE,'YYYY') AS NOWYEAR FROM DUAL; //获取时间的年
SELECT TO_CHAR(SYSDATE,'MM') AS NOWMONTH FROM DUAL; //获取时间的月
SELECT TO_CHAR(SYSDATE,'DD') AS NOWDAY FROM DUAL; //获取时间的日
SELECT TO_CHAR(SYSDATE,'HH24') AS NOWHOUR FROM DUAL; //获取时间的时
SELECT TO_CHAR(SYSDATE,'MI') AS NOWMINUTE FROM DUAL; //获取时间的分
SELECT TO_CHAR(SYSDATE,'SS') AS NOWSECOND FROM DUAL; //获取时间的秒
SELECT TO_DATE('2004-05-07 13:23:44','YYYY-MM-DD HH24:MI:SS') FROM DUAL//
2.
SELECT TO_CHAR(TO_DATE(222,'J'),'JSP') FROM DUAL
显示 TWO HUNDRED TWENTY-TWO
3.求某天是星期几
SELECT TO_CHAR(TO_DATE('2002-08-26','YYYY-MM-DD'),'DAY') FROM DUAL;
星期一
SELECT TO_CHAR(TO_DATE('2002-08-26','YYYY-MM-DD'),'DAY','NLS_DATE_LANGUAGE = AMERICAN') FROM DUAL;
MONDAY
设置日期语言
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
也可以这样
TO_DATE ('2002-08-26', 'YYYY-MM-DD', 'NLS_DATE_LANGUAGE = AMERICAN')
4.两个日期间的天数
SELECT FLOOR(SYSDATE - TO_DATE('20020405','YYYYMMDD')) FROM DUAL;
5.时间为NULL的用法
SELECT ID, ACTIVE_DATE FROM TABLE1
UNION
SELECT 1, TO_DATE(NULL) FROM DUAL;
注意要用TO_DATE(NULL)
6.月份差
A_DATE BETWEEN TO_DATE('20011201','YYYYMMDD') AND TO_DATE('20011231','YYYYMMDD')
那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
所以,当时间需要精确的时候,觉得TO_CHAR还是必要的
7. 日期格式冲突问题
输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, DATE格式的类型就是: '01-JAN-01'
ALTER SYSTEM SET NLS_DATE_LANGUAGE = AMERICAN
ALTER SESSION SET NLS_DATE_LANGUAGE = AMERICAN
或者在TO_DATE中写
SELECT TO_CHAR(TO_DATE('2002-08-26','YYYY-MM-DD'),'DAY','NLS_DATE_LANGUAGE = AMERICAN') FROM DUAL;
注意我这只是举了NLS_DATE_LANGUAGE,当然还有很多,
可查看
SELECT * FROM NLS_SESSION_PARAMETERS
SELECT * FROM V$NLS_PARAMETERS
8.
SELECT COUNT(*)
FROM ( SELECT ROWNUM-1 RNUM
FROM ALL_OBJECTS
WHERE ROWNUM <= TO_DATE('2002-02-28','YYYY-MM-DD') - TO_DATE('2002-
02-01','YYYY-MM-DD')+1
)
WHERE TO_CHAR( TO_DATE('2002-02-01','YYYY-MM-DD')+RNUM-1, 'D' ) NOT IN ( '1', '7' )
查找2002-02-28至2002-02-01间除星期一和七的天数
在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒).
9. 查找月份
SELECT MONTHS_BETWEEN(TO_DATE('01-31-1999','MM-DD-YYYY'),TO_DATE('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1
SELECT MONTHS_BETWEEN(TO_DATE('02-01-1999','MM-DD-YYYY'),TO_DATE('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1.03225806451613
10. NEXT_DAY的用法
NEXT_DAY(DATE, DAY)
MONDAY-SUNDAY, FOR FORMAT CODE DAY
MON-SUN, FOR FORMAT CODE DY
1-7, FOR FORMAT CODE D
11
SELECT TO_CHAR(SYSDATE,'HH:MI:SS') TIME FROM ALL_OBJECTS
注意:第一条记录的TIME 与最后一行是一样的
可以建立一个函数来处理这个问题
CREATE OR REPLACE FUNCTION SYS_DATE RETURN DATE IS
BEGIN
RETURN SYSDATE;
END;
SELECT TO_CHAR(SYS_DATE,'HH:MI:SS') FROM ALL_OBJECTS;
12.获得小时数
EXTRACT()找出日期或间隔值的字段值
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') FROM OFFER
SQL> SELECT SYSDATE ,TO_CHAR(SYSDATE,'HH') FROM DUAL;
SYSDATE TO_CHAR(SYSDATE,'HH')
-------------------- ---------------------
2003-10-13 19:35:21 07
SQL> SELECT SYSDATE ,TO_CHAR(SYSDATE,'HH24') FROM DUAL;
SYSDATE TO_CHAR(SYSDATE,'HH24')
-------------------- -----------------------
2003-10-13 19:35:21 19
13.年月日的处理
SELECT OLDER_DATE, NEWER_DATE, YEARS, MONTHS,
ABS(TRUNC(NEWER_DATE-ADD_MONTHS( OLDER_DATE,YEARS*12+MONTHS ))) DAYS
FROM
(
SELECT
TRUNC(MONTHS_BETWEEN( NEWER_DATE, OLDER_DATE )/12) YEARS,
MOD(TRUNC(MONTHS_BETWEEN( NEWER_DATE, OLDER_DATE )),12 ) MONTHS,
NEWER_DATE,
OLDER_DATE
FROM
(
SELECT HIREDATE OLDER_DATE, ADD_MONTHS(HIREDATE,ROWNUM)+ROWNUM NEWER_DATE FROM EMP
)
)
14.处理月份天数不定的办法
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE) +1, -2), 'YYYYMMDD'),LAST_DAY(SYSDATE) FROM DUAL
16.找出今年的天数
SELECT ADD_MONTHS(TRUNC(SYSDATE,'YEAR'), 12) - TRUNC(SYSDATE,'YEAR') FROM DUAL
闰年的处理方法
TO_CHAR( LAST_DAY( TO_DATE('02' | | :YEAR,'MMYYYY') ), 'DD' )
如果是28就不是闰年
17.YYYY与RRRR的区别
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18.不同时区的处理
SELECT TO_CHAR( NEW_TIME( SYSDATE, 'GMT','EST'), 'DD/MM/YYYY HH:MI:SS') ,SYSDATE
FROM DUAL;
19.5秒钟一个间隔
SELECT TO_DATE(FLOOR(TO_CHAR(SYSDATE,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(SYSDATE,'SSSSS')
FROM DUAL
2002-11-1 9:55:00 35786
SSSSS表示5位秒数
20.一年的第几天
SELECT TO_CHAR(SYSDATE,'DDD'),SYSDATE FROM DUAL
310 2002-11-6 10:03:51
21.计算小时,分,秒,毫秒
SELECT
DAYS,
A,
TRUNC(A*24) HOURS,
TRUNC(A*24*60 - 60*TRUNC(A*24)) MINUTES,
TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) SECONDS,
TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) MSECONDS
FROM
(
SELECT
TRUNC(SYSDATE) DAYS,
SYSDATE - TRUNC(SYSDATE) A
FROM DUAL
)
SELECT * FROM TABNAME
ORDER BY DECODE(MODE,'FIFO',1,-1)*TO_CHAR(RQ,'YYYYMMDDHH24MISS');
//
FLOOR((DATE2-DATE1) /365) 作为年
FLOOR((DATE2-DATE1, 365) /30) 作为月
D(MOD(DATE2-DATE1, 365), 30)作为日.
23.NEXT_DAY函数 返回下个星期的日期,DAY为1-7或星期日-星期六,1表示星期日
NEXT_DAY(SYSDATE,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。
1 2 3 4 5 6 7
日 一 二 三 四 五 六
---------------------------------------------------------------
SELECT (SYSDATE-TO_DATE('2003-12-03 12:55:45','YYYY-MM-DD HH24:MI:SS'))*24*60*60 FROM DDUAL
日期 返回的是天 然后 转换为SS
24,ROUND[舍入到最接近的日期](DAY:舍入到最接近的星期日)
SELECT SYSDATE S1,
ROUND(SYSDATE) S2 ,
ROUND(SYSDATE,'YEAR') YEAR,
ROUND(SYSDATE,'MONTH') MONTH ,
ROUND(SYSDATE,'DAY') DAY FROM DUAL
25,TRUNC[截断到最接近的日期,单位为天] ,返回的是日期类型
SELECT SYSDATE S1,
TRUNC(SYSDATE) S2, //返回当前日期,无时分秒
TRUNC(SYSDATE,'YEAR') YEAR, //返回当前年的1月1日,无时分秒
TRUNC(SYSDATE,'MONTH') MONTH , //返回当前月的1日,无时分秒
TRUNC(SYSDATE,'DAY') DAY //返回当前星期的星期天,无时分秒
FROM DUAL
26,返回日期列表中最晚日期
SELECT GREATEST('01-1月-04','04-1月-04','10-2月-04') FROM DUAL
27.计算时间差
注:ORACLE时间差是以天数为单位,所以换算成年月,日
SELECT FLOOR(TO_NUMBER(SYSDATE-TO_DATE('2007-11-02 15:55:03','YYYY-MM-DD HH24:MI:SS'))/365) AS SPANYEARS FROM DUAL //时间差-年
SELECT CEIL(MOTHS_BETWEEN(SYSDATE-TO_DATE('2007-11-02 15:55:03','YYYY-MM-DD HH24:MI:SS'))) AS SPANMONTHS FROM DUAL //时间差-月
SELECT FLOOR(TO_NUMBER(SYSDATE-TO_DATE('2007-11-02 15:55:03','YYYY-MM-DD HH24:MI:SS'))) AS SPANDAYS FROM DUAL //时间差-天
SELECT FLOOR(TO_NUMBER(SYSDATE-TO_DATE('2007-11-02 15:55:03','YYYY-MM-DD HH24:MI:SS'))*24) AS SPANHOURS FROM DUAL //时间差-时
SELECT FLOOR(TO_NUMBER(SYSDATE-TO_DATE('2007-11-02 15:55:03','YYYY-MM-DD HH24:MI:SS'))*24*60) AS SPANMINUTES FROM DUAL //时间差-分
SELECT FLOOR(TO_NUMBER(SYSDATE-TO_DATE('2007-11-02 15:55:03','YYYY-MM-DD HH24:MI:SS'))*24*60*60) AS SPANSECONDS FROM DUAL //时间差-秒
28.更新时间
注:ORACLE时间加减是以天数为单位,设改变量为N,所以换算成年月,日
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(SYSDATE+N*365,'YYYY-MM-DD HH24:MI:SS') AS NEWTIME FROM DUAL //改变时间-年
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),ADD_MONTHS(SYSDATE,N) AS NEWTIME FROM DUAL //改变时间-月
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(SYSDATE+N,'YYYY-MM-DD HH24:MI:SS') AS NEWTIME FROM DUAL //改变时间-日
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(SYSDATE+N/24,'YYYY-MM-DD HH24:MI:SS') AS NEWTIME FROM DUAL //改变时间-时
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(SYSDATE+N/24/60,'YYYY-MM-DD HH24:MI:SS') AS NEWTIME FROM DUAL //改变时间-分
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(SYSDATE+N/24/60/60,'YYYY-MM-DD HH24:MI:SS') AS NEWTIME FROM DUAL //改变时间-秒
29.查找月的第一天,最后一天
SELECT TRUNC(TRUNC(SYSDATE, 'MONTH') - 1, 'MONTH') FIRST_DAY_LAST_MONTH,
TRUNC(SYSDATE, 'MONTH') - 1 / 86400 LAST_DAY_LAST_MONTH,
TRUNC(SYSDATE, 'MONTH') FIRST_DAY_CUR_MONTH,
LAST_DAY(TRUNC(SYSDATE, 'MONTH')) + 1 - 1 / 86400 LAST_DAY_CUR_MONTH
FROM DUAL;
三. 字符函数(可用于字面字符或数据库列)
1,字符串截取
SELECT SUBSTR('ABCDEF',1,3) FROM DUAL
2,查找子串位置
SELECT INSTR('ABCFDGFDHD','FD') FROM DUAL
3,字符串连接
SELECT 'HELLO'||'HELLO WORLD' FROM DUAL;
4, 1)去掉字符串中的空格
SELECT LTRIM(' ABC') S1,
RTRIM('ZHANG ') S2,
TRIM(' ZHANG ') S3 FROM DUAL
2)去掉前导和后缀
SELECT TRIM(LEADING 9 FROM 9998767999) S1,
TRIM(TRAILING 9 FROM 9998767999) S2,
TRIM(9 FROM 9998767999) S3 FROM DUAL;
5,返回字符串首字母的ASCII值
SELECT ASCII('A') FROM DUAL
6,返回ASCII值对应的字母
SELECT CHR(97) FROM DUAL
7,计算字符串长度
SELECT LENGTH('ABCDEF') FROM DUAL
8,INITCAP(首字母变大写) ,LOWER(变小写),UPPER(变大写)
SELECT LOWER('ABC') S1,
UPPER('DEF') S2,
INITCAP('EFG') S3
FROM DUAL;
9,REPLACE
SELECT REPLACE('ABC','B','XY') FROM DUAL;
10,TRANSLATE
SELECT TRANSLATE('ABC','B','XX') FROM DUAL; -- X是1位
11,LPAD [左添充] RPAD [右填充](用于控制输出格式)
SELECT LPAD('FUNC',15,'=') S1, RPAD('FUNC',15,'-') S2 FROM DUAL;
SELECT LPAD(DNAME,14,'=') FROM DEPT;
12, DECODE[实现IF ..THEN 逻辑] 注:第一个是表达式,最后一个是不满足任何一个条件的值
SELECT DEPTNO,DECODE(DEPTNO,10,'1',20,'2',30,'3','其他') FROM DEPT;
例:
SELECT SEED,ACCOUNT_NAME,DECODE(SEED,111,1000,200,2000,0) FROM T_USERINFO//如果SEED为111,则取1000;为200,取2000;其它取0
SELECT SEED,ACCOUNT_NAME,DECODE(SIGN(SEED-111),1,'BIG SEED',-1,'LITTLE SEED','EQUAL SEED') FROM T_USERINFO
//如果SEED>111,则显示大;为200,则显示小;其它则显示相等
<pre name="code" class="html">13 CASE[实现SWITCH ..CASE 逻辑]
SELECT CASE X-FIELD
WHEN X-FIELD < 40 THEN 'X-FIELD 小于 40'
WHEN X-FIELD < 50 THEN 'X-FIELD 小于 50'
WHEN X-FIELD < 60 THEN 'X-FIELD 小于 60'
ELSE 'UNBEKNOWN'
END
FROM DUAL
注:CASE语句在处理类似问题就显得非常灵活。当只是需要匹配少量数值时,用DECODE更为简洁。
四.数字函数
1,取整函数(CEIL 向上取整,FLOOR 向下取整)
SELECT CEIL(66.6) N1,FLOOR(66.6) N2 FROM DUAL;
2, 取幂(POWER) 和 求平方根(SQRT)
SELECT POWER(3,2) N1,SQRT(9) N2 FROM DUAL;
3,求余
SELECT MOD(9,5) FROM DUAL;
4,返回固定小数位数 (ROUND:四舍五入,TRUNC:直接截断)
SELECT ROUND(66.667,2) N1,TRUNC(66.667,2) N2 FROM DUAL;
5,返回值的符号(正数返回为1,负数为-1)
SELECT SIGN(-32),SIGN(293) FROM DUAL;
五.转换函数
1,TO_CHAR()[将日期和数字类型转换成字符类型]
1) SELECT TO_CHAR(SYSDATE) S1,
TO_CHAR(SYSDATE,'YYYY-MM-DD') S2,
TO_CHAR(SYSDATE,'YYYY') S3,
TO_CHAR(SYSDATE,'YYYY-MM-DD HH12:MI:SS') S4,
TO_CHAR(SYSDATE, 'HH24:MI:SS') S5,
TO_CHAR(SYSDATE,'DAY') S6
FROM DUAL;
2) SELECT SAL,TO_CHAR(SAL,'$99999') N1,TO_CHAR(SAL,'$99,999') N2 FROM EMP
2, TO_DATE()[将字符类型转换为日期类型]
INSERT INTO EMP(EMPNO,HIREDATE) VALUES(8000,TO_DATE('2004-10-10','YYYY-MM-DD'));
3, TO_NUMBER() 转换为数字类型
SELECT TO_NUMBER(TO_CHAR(SYSDATE,'HH12')) FROM DUAL; //以数字显示的小时数
六.其他函数
1.USER:
返回登录的用户名称
SELECT USER FROM DUAL;
2.VSIZE:
返回表达式所需的字节数
SELECT VSIZE('HELLO') FROM DUAL;
3.NVL(EX1,EX2):
EX1值为空则返回EX2,否则返回该值本身EX1(常用)
例:如果雇员没有佣金,将显示0,否则显示佣金
SELECT COMM,NVL(COMM,0) FROM EMP;
4.NULLIF(EX1,EX2):
值相等返空,否则返回第一个值
例:如果工资和佣金相等,则显示空,否则显示工资
SELECT NULLIF(SAL,COMM),SAL,COMM FROM EMP;
5.COALESCE:
返回列表中第一个非空表达式
SELECT COMM,SAL,COALESCE(COMM,SAL,SAL*10) FROM EMP;
6.NVL2(EX1,EX2,EX3) :
如果EX1不为空,显示EX2,否则显示EX3
如:查看有佣金的雇员姓名以及他们的佣金
SELECT NVL2(COMM,ENAME,') AS HAVECOMMNAME,COMM FROM EMP;
七.分组函数
MAX MIN AVG COUNT SUM
1,整个结果集是一个组
1) 求部门30 的最高工资,最低工资,平均工资,总人数,有工作的人数,工种数量及工资总和
SELECT MAX(ENAME),MAX(SAL),
MIN(ENAME),MIN(SAL),
AVG(SAL),
COUNT(*) ,COUNT(JOB),COUNT(DISTINCT(JOB)) ,
SUM(SAL) FROM EMP WHERE DEPTNO=30;
2, 带GROUP BY 和 HAVING 的分组
1)按部门分组求最高工资,最低工资,总人数,有工作的人数,工种数量及工资总和
SELECT DEPTNO, MAX(ENAME),MAX(SAL),
MIN(ENAME),MIN(SAL),
AVG(SAL),
COUNT(*) ,COUNT(JOB),COUNT(DISTINCT(JOB)) ,
SUM(SAL) FROM EMP GROUP BY DEPTNO;
2)部门30的最高工资,最低工资,总人数,有工作的人数,工种数量及工资总和
SELECT DEPTNO, MAX(ENAME),MAX(SAL),
MIN(ENAME),MIN(SAL),
AVG(SAL),
COUNT(*) ,COUNT(JOB),COUNT(DISTINCT(JOB)) ,
SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO=30;
3, STDDEV 返回一组值的标准偏差
SELECT DEPTNO,STDDEV(SAL) FROM EMP GROUP BY DEPTNO;
VARIANCE 返回一组值的方差差
SELECT DEPTNO,VARIANCE(SAL) FROM EMP GROUP BY DEPTNO;
4, 带有ROLLUP和CUBE操作符的GROUP BY
ROLLUP 按分组的第一个列进行统计和最后的小计
CUBE 按分组的所有列的进行统计和最后的小计
SELECT DEPTNO,JOB ,SUM(SAL) FROM EMP GROUP BY DEPTNO,JOB;
SELECT DEPTNO,JOB ,SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO,JOB);
CUBE 产生组内所有列的统计和最后的小计
SELECT DEPTNO,JOB ,SUM(SAL) FROM EMP GROUP BY CUBE(DEPTNO,JOB);
八、临时表
只在会话期间或在事务处理期间存在的表.
临时表在插入数据时,动态分配空间
CREATE GLOBAL TEMPORARY TABLE TEMP_DEPT
(DNO NUMBER,
DNAME VARCHAR2(10))
ON COMMIT DELETE ROWS;
INSERT INTO TEMP_DEPT VALUES(10,'ABC');
COMMIT;
SELECT * FROM TEMP_DEPT; --无数据显示,数据自动清除
ON COMMIT PRESERVE ROWS:在会话期间表一直可以存在(保留数据)
ON COMMIT DELETE ROWS:事务结束清除数据(在事务结束时自动删除表的数据)