Oracle 日期函数和转换函数
一、日期函数
日期函数用于处理date类型的数据,两个日期相减返回日期之间相差的天数。日期不允许做加法运算,无意义。
常见代表符号:yyyy 年,mm 月,dd 日,hh 小时,mi 分钟,ss 秒,day 星期 默认情况下日期格式是dd-mon-yy即12-3月-19 (1)sysdate: 该函数返回系统时间 (2)months_between(m,n)日期m和日期n相差多少月数 (3)add_months(d,n)在日期d上增加n个月数 (4)next_day(d, '星期*') 指定日期d下一个星期*对应的日期 (5)last_day(d):返回指定日期d所在月份的最后一天 (6)extract(month from d)从日期d上提取月份数 (7)round(d,time)日期的四舍五入 (8)trunc(d,time)日期的截断
以下是日期函数的一些例子及效果图:
各种情况例子:
months_between select months_between('01-9月-95','11-1月-94') from dual; --19.6774193548387 add_months select add_months('11-2月-18',6) from dual; --2018/8/11 next_day select next_day('11-2月-18','星期六') from dual; --2018/2/17 last_day select last_day('11-2月-18') from dual; --2018/2/28 round 四舍五入月份 25-7月-18 select round(to_date('25-7月-2018'), 'month') from dual; --2018/8/1 round 四舍五入年份 25-7月-18 select round(to_date('25-7月-2018’), 'year') from dual; --2019/1/1 trunc 截断月份 25-7月-18 select trunc(to_date('25-7月-2018'), 'month') from dual; --2018/7/1 trunc 截断年份 25-7月-18 select trunc(to_date('25-7月-2018'), 'year') from dual; --2018/1/1
trunc 当前日期的00点00分00秒 select trunc(sysdate) from dual;
sql比较日期
今天之前:
select * from table where update < to_date('2012-09-07 00:00:00', 'yyyy-MM-dd HH24:mi:ss');
精确时间:
select * from table where update = to_date('2012-09-07 00:00:00', 'yyyy-MM-dd HH24:mi:ss');
某段时间内:
select * from table where update <= to_date('2012-09-07 00:00:00', 'yyyy-MM-dd HH24:mi:ss')
and update >= to_date('2010-02-07 00:00:00', 'yyyy-MM-dd HH24:mi:ss')
eg:查找已经入职8个月多的员工
SQL>
select * from emp where sysdate>=add_months(hiredate,8);
eg:显示满10年服务年限的员工的姓名和受雇日期。
SQL>
select ename, hiredate from emp where sysdate>=add_months(hiredate,12*10);
eg:对于每个员工,显示其加入公司的天数。
SQL> select floor(sysdate-hiredate),ename from emp;
或者
SQL> select trunc(sysdate-hiredate),ename from emp;
eg:找出各月倒数第3天受雇的所有员工。
SQL>select hiredate,ename from emp where last_day(hiredate)-2=hiredate;
二、转换函数
转换函数用于将数据类型从一种转为另外一种。在某些情况下,oracle server允许值的数据类型和实际的不一样,这时oracle server会隐含的转化数据类型
我们要说的是尽管oracle可以进行隐含的数据类型的转换,但是它并不适应所有的情况,为了提高程序的可靠性,我们应该使用转换函数进行转换。
(1)to_char函数
格式: to_char(date,‘format’)
1、必须包含在单引号中而且大小写敏感。
2、可以包含任意的有效的日期格式。
3、日期之间用逗号隔开。
eg:日期是否可以显示 时/分/秒
SQL> select ename, to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
eg:薪水是否可以显示指定的货币符号
SQL> select sal,to_char(sal,'$999,999.99') from emp;
yy:两位数字的年份2004–>04
yyyy:四位数字的年份 2004年
mm:两位数字的月份 8月–>08
dd:两位数字的天 30号–>30
hh24: 8点–>20
hh12:8点–>08
mi、ss–>显示分钟/秒
9:显示数字,并忽略前面0
0:显示数字,如位数不足,则用0补齐
.:(小数点)在指定位置显示小数点
,:(千位符)在指定位置显示逗号
$:(美元符)在数字前加美元
L:(本地货币符)在数字前面加本地货币符号
C:(国际货币符)在数字前面加国际货币符号
eg:显示薪水的时候,把本地货币单位加在前面
SQL> select ename, to_char(sal,'L99999.99')from emp;
eg:显示1980年入职的所有员工
SQL> select * from emp where to_char(hiredate, 'yyyy')=1980;
eg:显示所有12月份入职的员工
SQL> select * from emp where to_char(hiredate, 'mm')=12;
这里的12和1980是数字,可以加 ’ ’ 也可以不加,因为Oracle会自动转换,但是最好加。
eg:显示姓名、hiredate和雇员开始工作日是星期几
SQL> select ename,hiredate,to_char(hiredate,'day') from emp;
(2)to_date函数
格式:to_date(string,‘format’)
函数to_date用于将字符串转换成date类型的数据。
eg:把字符串2015-03-18 13:13:13转换成日期格式,
SQL> select to_date('2015-03-18 13:13:13','yyyy-mm-dd hh24:mi:ss') from dual;
(3)to_number函数
格式:to_number(char,‘format’)
使用to_number函数将字符转换成日期。
SQL> select to_number('¥1,234,567,890.00','L999,999,999,999.99') from dual;