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 截断日期
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
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