SQL-日期函数

日期函数

处理日期

• Oracle DB 以内部数字格式存储日期：世纪、年、月、日、小时、分钟和秒。
• 默认的日期显示格式为DD-MON-RR。

问题：为什么不使用”日期+数字”，而是用add_months
答：需要考虑闰年的问题

函数	结果	说明
months_between(‘2016-10-1’,’2016-1-1’)	9	两个日期之间的月数
add_months(sysdate, 24)	2018-11-01 08:30:01	将日历月添加到日期
NEXT_DAY(sysdate,’monday’)	2016-11-07 08:46:24	指定日期之后的下一个日期
LAST_DAY(sysdate)	2016-11-30 08:47:41	当月最后一天
extract(year from date ‘2016-11-1’)	2016	或者时间间隔（INTERVAL）中截取出特定的部分
ROUND(2016-11-02,’YEAR’)	2017-01-01	舍入日期
TRUNC(2016-11-02,’YEAR’)	2016-01-01	截断日期

SELECT last_name, round((SYSDATE-hire_date)/7) AS WEEKS
FROM employees
WHERE department_id = 90;

LAST_NAME                      WEEKS
------------------------- ----------
King                             562
Kochhar                          444
De Haan                          689


select months_between('2016-10-1','2016-1-1') from dual;

MONTHS_BETWEEN('2016-10-1','2016-1-1')
--------------------------------------
                                     9

select sysdate, add_months(sysdate, 24) from dual;

SYSDATE             ADD_MONTHS(SYSDATE,
------------------- -------------------
2016-11-01 08:30:01 2018-11-01 08:30:01


select sysdate, next_day(sysdate,'monday') from dual;

SYSDATE             NEXT_DAY(SYSDATE
------------------- -------------------
2016-11-01 08:46:24 2016-11-07 08:46:24

select sysdate, last_day(sysdate) from dual;

SYSDATE             LAST_DAY(SYSDATE)
------------------- -------------------
2016-11-01 08:47:41 2016-11-30 08:47:41

select extract(year from date '2016-11-1') year,
       extract(month from date '2016-11-1') month,
       extract(day from date '2016-11-1') day
from dual;

      YEAR      MONTH        DAY
---------- ---------- ----------
      2016         11          1

select extract(day from datatime_one - datatime_two) days,
       extract(hour from datatime_one - datatime_two) hours, 
       extract(minute from datatime_one - datatime_two) minutes,
       extract(second from datatime_one - datatime_two) seconds
 from
( 
  select to_timestamp('1982-08-13 12:17:57', 'yyyy-mm-dd hh24:mi:ss') datatime_one,
         to_timestamp('1981-09-27 09:08:33', 'yyyy-mm-dd hh24:mi:ss') datatime_two
from dual);

      DAYS      HOURS    MINUTES    SECONDS
---------- ---------- ---------- ----------
       320          3          9         24

select sysdate, ROUND(SYSDATE ,'YEAR') year, round(sysdate,'MONTH') from dual;

SYSDATE             YEAR                ROUND(SYSDATE,'MONT
------------------- ------------------- -------------------
2016-11-02 06:04:48 2017-01-01 00:00:00 2016-11-01 00:00:00

select sysdate, TRUNC(SYSDATE ,'YEAR') year, TRUNC(sysdate,'MONTH') from dual;

SYSDATE             YEAR                TRUNC(SYSDATE,'MONT
------------------- ------------------- -------------------
2016-11-02 06:05:19 2016-01-01 00:00:00 2016-11-01 00:00:00


--日期格式显示：
SELECT *
    FROM wbxemailtask
   WHERE createtime < TO_DATE ('2012-1-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY createtime DESC;

select to_char( sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

select sysdate from dual;

SYSDATE
---------
01-NOV-16

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

select sysdate from dual;

SYSDATE
-------------------
2016-11-01 08:11:54

/**
日期运算：
日期 - 数字 = 日期
日期 + 数字 = 日期
日期 - 日期 = 数字
日期 + 日期 = 不允许，会报错
**/

select sysdate+3 from dual;

SYSDATE+3
-------------------
2016-11-04 08:16:41

select sysdate-3 from dual;

SYSDATE-3
-------------------
2016-10-29 08:16:46

select employee_id, sysdate - hire_date from employees;

EMPLOYEE_ID SYSDATE-HIRE_DATE
----------- -----------------
        199        3215.34737
        200        4794.34737
        201        4641.34737

select sysdate+sysdate from dual;
select sysdate+sysdate from dual
              *
ERROR at line 1:
ORA-00975: date + date not allowed

--查询出每个雇员的ID，name, 雇佣日期，已雇佣的年数，月数，天数
select employee_id, first_name, hire_date, 
    trunc(months_between(sysdate,hire_date)/12) year ,    
    trunc(mod(months_between(sysdate,hire_date),12)) month, 
    trunc(sysdate-add_months(hire_date, months_between(sysdate,hire_date))) day 
    from employees where rownum < 10;

EMPLOYEE_ID FIRST_NAME                HIRE_DATE                 YEAR      MONTH        DAY
----------- ------------------------- ------------------- ---------- ---------- ----------
        199 Douglas                   2008-01-13 00:00:00          8          9         19
        200 Jennifer                  2003-09-17 00:00:00         13          1         15
        201 Michael                   2004-02-17 00:00:00         12          8         15
        202 Pat                       2005-08-17 00:00:00         11          2         15
        203 Susan                     2002-06-07 00:00:00         14          4         25
        204 Hermann                   2002-06-07 00:00:00         14          4         25
        205 Shelley                   2002-06-07 00:00:00         14          4         25
        206 William                   2002-06-07 00:00:00         14          4         25
        182 Martha                    2007-06-21 00:00:00          9          4         11