ORACLE日期时间函数大全
TO_DATE格式(以时间:2007-11-02 13:45:25为例)
Year:
yy two digits 两位年 显示值:07
yyy three digits 三位年 显示值:007
yyyy four digits 四位年 显示值:2007
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:
hh two digits 12小时进制 显示值:01
hh24 two digits 24小时进制 显示值:13
Minute:
mi two digits 60进制 显示值:45
Second:
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; //获取时间的秒
日期函数对日期值进行运算,并生成日期数据类型或数值类型的结果
日期函数包括:
ADD_MONTHS(d,n)时间点d再加上n个月
select sysdate ,add_months(sysdate,2) aa from dual;
SYSDATE AA
----------- -----------
2014/9/24 1 2014/11/24
MONTHS_BETWEEN;用于计算有多少个月
MONTHS_BETWEEN (x, y)用于计算x和y之间有几个月。如果x在日历中比y早,那么MONTHS_BETWEEN()就返回一个负数。
当x 和 y 之间的月份之差不是整月的时候,可以采用小数表示,例如
SELECT MONTHS_BETWEEN('25-MAY-2008', '15-JAN-2008')
FROM dual;
MONTHS_BETWEEN('25-MAY-2008','15-JAN-2008')
4.32258065
months_between(date1, date2),必须注意的是,date1与date2都为Date类型,不然会出现错误。须用to_date(‘’,'') 来转换为日期格式,才能参加计算。
LAST_DAY ;last_day是个函数,返回某个时间值的当月最后一天
select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-----------------
2014/9/30 14:35:5
ROUND; ROUND 函数用于把数值字段舍入为指定的小数位数。
SELECT ROUND(column_name,decimals) FROM table_name
参数 | 描述 |
---|---|
column_name | 必需。要舍入的字段。 |
decimals | 必需。规定要返回的小数位数。 |
显示在一个月为30天,所有员工的日薪,忽略余数
select ename round(sal/30) from emp;
NEXT_DAY
next_day(x,y)用于计算x时间后第一个星期y的时间。
例子,当前时间是2011-11-29 下午 03:19:32 星期二:
select next_day(sysdate,"星期三")from dual;
返回的结果是: 2011-11-30 下午 03:19:32
语法
TRUNC(number,[num_digits])
函数语法参数
TRUNC 函数语法具有下列参数:
Number 必需。需要截尾取整的数字。
Num_digits 可选。用于指定取整精度的数字。
Num_digits 的默认值为 0(零)。
trunc(sysdate,'yyyy') --返回当年第一天.
trunc(sysdate,'mm') --返回当月第一天.
trunc(sysdate,'d') --返回当前星期的第一天.
EXTRACT
习题;
EMP表结构:员工编号、员工姓名、职位、上司、入职时间、工资、奖金、部门编号
DEPT 部门表:部门编号、部门名称、部门所在位置(Location)
BOUNS 奖金表:姓名、职务、工资、奖金
1、选择部门30中所有的员工
select ename ,deptno from emp where deptno=30 ;
2、列出所有业务人员(CLERK)的姓名,编号,和部门编号。
select job,ename,empno,deptno from emp where job = 'CLERK';
3、找出佣金高于薪水的员工
select ename ,sal ,comm from emp where comm>sal;
4、找出佣金高于薪水60%的员工
select ename ,sal ,comm from emp where comm*(1+0.6)>sal;
5、找出部门10中所有的经理和部门20中所有业务员的详细资料
select ename,sal,job from emp where job='MANAGER' and OR select ename,deptno from emp where job = 'SALESMAN' and
deptno =30;
6、找出部门10中所有的经理和部门20中所有业务员,既不是经理又不是业务员但薪水大于等于2000的所有的员工信息
select ename ,sal ,job ,deptno from emp where not job ='MANAGER'and deptno = 10 or not job = 'SALESMAN' and
deptno =30 and sal> 2000;
7、找出收取佣金的员工的不同工作;
select ename ,deptno from emp where job in(select job from emp where comm >0);
8、找出收取佣金或收取佣金低于100的员工;
select ename from emp where comm > 0 and comm<100;
9、找出各月倒数第三天受雇的所有员工。
select * from emp where hiredate = last_day(hiredate)-2;
10、找出早于81年11月15日之后入职的受雇员工
select * from emp where hiredate<'17-11月 -81';
11、以首字母大写方式显示所有的员工姓名;
select initcap(ename) 姓名 from emp;
12、显示正好为5个字符的员工姓名;
select ename from emp where ename like '_____';
13、显示不带R的员工姓名
select ename from emp where ename not like '%R%';
14、显示所有员工姓名的前三个字符
select substr(ename,1,3) ename from emp ;
15、显示所有员工的姓名,用”a”代替所有的“A”
select replace(ename ,'A','a') ename from emp;
16、显示满10年服务年限的员工姓名和受雇日期
select ename hiredate from emp where months_between(sysdate,hiredate)/12>10;
17、按名字排序员工
select ename from emp order by ename ;
18、显示员工的姓名,受雇日期,根据服务年限按老员工排列
select ename,hiredate from emp order by hiredate ;
19、显示在一个月为30天,所有员工的日薪,忽略余数
select ename round(sal/30) from emp;
20、找出在2月份受雇的员工
select * from emp where to_char(hiredate,'mm')=2;
21、显示每个员工加入公司的天数
select ename ,round(sysdate-hiredate)days from emp;
22、找出名字包含“A”的员工
select ename from emp where ename like '%A%';