1
--
--------------------------------------------------------------------------
2
--
----------oracle time ------------
3
--
---------------------------------------------------------------------------
4
/*
5
Year:
6
yy two digits 两位年 显示值:12
7
yyy three digits 三位年 显示值:012
8
yyyy four digits 四位年 显示值:2012
9
10
Month:
11
mm number 两位月 显示值:12
12
mon abbreviated 字符集表示 显示值:12月,若是英文版,显示
13
month spelled out 字符集表示 显示值:12月,若是英文版,显示
14
15
Day:
16
dd number 当月第几天 显示值:
17
ddd number 当年第几天 显示值:
18
dy abbreviated 当周第几天简写 显示值:,若是英文版,显示
19
day spelled out 当周第几天全写 显示值:,若是英文版,显示
20
ddspth spelled out, ordinal twelfth
21
22
Hour:
23
hh two digits 12小时进制 显示值:
24
hh24 two digits 24小时进制 显示值:
25
26
Minute:
27
mi two digits 60进制 显示值:
28
29
Second:
30
ss two digits 60进制 显示值:
31
32
其它：
33
Q digit 季度 显示值:
34
WW digit 当年第几周 显示值:
35
W digit 当月第几周 显示值:
36
37
24小时格式下时间范围为： 0:00:00 - 23:59:59....
38
12小时格式下时间范围为： 1:00:00 - 12:59:59 ....
39
*/
40
--
1.得到当前时间
41
--
英文：
42
SELECT
TO_CHAR(sysdate,
43
'
MM/DD/YYYY HH:MI AM
'
,
44
'
NLS_DATE_LANGUAGE = American
'
)
45
FROM
DUAL
46
--
日文：
47
SELECT
TO_CHAR(sysdate,
48
'
MM/DD/YYYY HH:MI AM
'
,
49
'
NLS_DATE_LANGUAGE = Japanese
'
)
50
FROM
DUAL
51
--
中文：（特别注意NLS_DATE_LANGUAGE多个单引号）
52
SELECT
TO_CHAR(sysdate,
53
'
MM/DD/YYYY HH:MI AM
'
,
54
'
NLS_DATE_LANGUAGE =
''
SIMPLIFIED CHINESE
'''
)
55
FROM
DUAL
56
57
--
得到系统时间
58
select
to_char(sysdate,
'
yyyy-mm-dd hh24:mi:ss
'
)
as
nowTime
from
dual;
59
--
获取时间的年
60
select
to_char(sysdate,
'
yyyy
'
)
as
nowYear
from
dual;
61
--
获取时间的月
62
select
to_char(sysdate,
'
mm
'
)
as
nowMonth
from
dual;
63
--
获取时间的日
64
select
to_char(sysdate,
'
dd
'
)
as
nowDay
from
dual;
65
--
获取时间的时
66
select
to_char(sysdate,
'
hh24
'
)
as
nowHour
from
dual;
67
--
获取时间的分
68
select
to_char(sysdate,
'
mi
'
)
as
nowMinute
from
dual;
69
--
获取时间的秒
70
select
to_char(sysdate,
'
ss
'
)
as
nowSecond
from
dual;
71
--
2.显示成英文字符
72
--
显示成文英文数字
73
select
to_char( to_date(
2012
,
'
J
'
),
'
Jsp
'
)
from
dual;
74
--
结果为： Two Thousand Twelve
75
SELECT
to_char(to_date(
12
,
'
J
'
),
'
Jsp
'
)
FROM
dual;
76
--
3.求某天是星期几
77
select
to_char(to_date(
'
2012-12-26
'
,
'
yyyy-mm-dd
'
),
'
day
'
)
DAY
from
dual;
78
--
显示为星期三
79
SELECT
to_char(to_date(
'
2012-12-26
'
,
'
yyyy-mm-dd
'
),
'
day
'
,
'
NLS_DATE_LANGUAGE = American
'
)
DAY
FROM
dual;
80
--
显示为wednesday
81
--
更改显示语言为american
82
ALTER
SESSION
SET
NLS_DATE_LANGUAGE
=
'
AMERICAN
'
;
83
--
设置为简体中文
84
ALTER
SESSION
SET
NLS_DATE_LANGUAGE
=
'
SIMPLIFIED CHINESE
'
;
85
86
--
4.求两个日期间的天数
87
SELECT
FLOOR
(SYSDATE
-
to_date(
'
20121201
'
,
'
yyyymmdd
'
))
FROM
dual;
88
89
--
5. 时间为null的用法
90
CREATE
TABLE
tTime
91
( ID
NUMBER
,
92
TIME DATE
93
);
94
INSERT
INTO
tTime(ID,TIME)
VALUES
(
1
,to_date(
'
2012-12-12
'
,
'
yyyy-mm-dd
'
));
95
INSERT
INTO
tTime(ID)
VALUES
(
2
);
96
SELECT
*
FROM
tTime;
97
98
CREATE
TABLE
nTime
AS
SELECT
*
FROM
tTime;
99
SELECT
*
FROM
nTIme;
100
101
select
ID, TIME
from
tTime
102
UNION
103
SELECT
ID ,TO_DATE(
null
)
from
nTime;
104
105
--
6. 日期格式冲突问题
106
--
输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01'
107
alter
system
set
NLS_DATE_LANGUAGE
=
American;
108
alter
session
set
NLS_DATE_LANGUAGE
=
American;
109
--
或者在to_date中写
110
select
to_char(to_date(
'
2012-12-26
'
,
'
yyyy-mm-dd
'
),
'
day
'
,
'
NLS_DATE_LANGUAGE = American
'
)
from
dual;
111
--
注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，
112
--
可查看
113
select
*
from
nls_session_parameters;
114
select
*
from
V$NLS_PARAMETERS;
115
116
--
7.查找除去某些天的天数之和
117
select
count
(
*
)
118
from
(
select
rownum
-
1
rnum
119
from
all_objects
120
where
rownum
<=
to_date(
'
2013-01-01
'
,
'
yyyy-mm-dd
'
)
-
to_date(
'
2012-
121
01-01
'
,
'
yyyy-mm-dd
'
)
+
1
122
)
123
where
to_char( to_date(
'
2012-01-01
'
,
'
yyyy-mm-dd
'
)
+
rnum
-
1
,
'
D
'
)
124
not
in
(
'
1
'
,
'
7
'
)
125
126
--
查找2012-01-01至2013-01-01间除星期一和七的天数
127
--
在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒).
128
--
8. 查找月份
129
select
months_between(to_date(
'
01-31-2013
'
,
'
MM-DD-YYYY
'
),to_date(
'
12-31-2012
'
,
'
MM-DD-YYYY
'
)) "MONTHS"
FROM
DUAL;
130
131
select
months_between(to_date(
'
02-01-2013
'
,
'
MM-DD-YYYY
'
),to_date(
'
12-31-2012
'
,
'
MM-DD-YYYY
'
)) "MONTHS"
FROM
DUAL;
132
133
--
9.显示时间
134
select
to_char(sysdate,
'
hh:mi:ss
'
) TIME
from
all_objects;
135
--
函数实现
136
create
or
replace
function
sys_date
return
date
is
137
begin
138
return
sysdate;
139
end
;
140
select
to_char(sys_date,
'
hh:mi:ss
'
)
from
all_objects;
141
142
--
10.获得小时数
143
--
extract()找出日期或间隔值的字段值
144
SELECT
EXTRACT(HOUR
FROM
TIMESTAMP
'
2012-12-24 12:48:36
'
)
from
dual;
145
select
sysdate ,to_char(sysdate,
'
hh24
'
)
from
dual;
146
147
--
11.年月日的处理
148
select
older_date,
149
newer_date,
150
years,
151
months,
152
abs
(
153
trunc(
154
newer_date
-
155
add_months( older_date,years
*
12
+
months )
156
)
157
) days
158
159
from
(
select
160
trunc(months_between( newer_date, older_date )
/
12
) YEARS,
161
mod(trunc(months_between( newer_date, older_date )),
12
) MONTHS,
162
newer_date,
163
older_date
164
from
(
165
select
hiredate older_date, add_months(hiredate,rownum)
+
rownum newer_date
166
FROM
emp
167
)
168
);
169
--
12.处理月份天数不定的办法
170
select
to_char(add_months(last_day(sysdate)
+
1
,
-
2
),
'
yyyymmdd
'
),last_day(sysdate)
from
dual
171
172
173
--
13.找出今年的天数
174
select
add_months(trunc(sysdate,
'
year
'
),
12
)
-
trunc(sysdate,
'
year
'
)
from
dual
175
--
14.不同时区的处理
176
select
to_char( NEW_TIME( sysdate,
'
GMT
'
,
'
EST
'
),
'
dd/mm/yyyy hh:mi:ss
'
) ,sysdate
177
from
dual;
178
179
--
15.5秒钟一个间隔
180
Select
TO_DATE(
FLOOR
(TO_CHAR(sysdate,
'
SSSSS
'
)
/
300
)
*
300
,
'
SSSSS
'
) ,TO_CHAR(sysdate,
'
SSSSS
'
)
181
from
dual
182
--
16.一年的第几天
183
select
TO_CHAR(SYSDATE,
'
DDD
'
),sysdate
from
dual
184
--
17.计算小时,分,秒,毫秒
185
select
186
Days,
187
A,
188
TRUNC(A
*
24
) Hours,
189
TRUNC(A
*
24
*
60
-
60
*
TRUNC(A
*
24
)) Minutes,
190
TRUNC(A
*
24
*
60
*
60
-
60
*
TRUNC(A
*
24
*
60
)) Seconds,
191
TRUNC(A
*
24
*
60
*
60
*
100
-
100
*
TRUNC(A
*
24
*
60
*
60
)) mSeconds
192
from
193
(
194
select
195
trunc(sysdate) Days,
196
sysdate
-
trunc(sysdate) A
197
from
dual
198
)
199
--
18,round[舍入到最接近的日期](day:舍入到最接近的星期日)
200
select
sysdate S1,
201
round
(sysdate) S2 ,
202
round
(sysdate,
'
year
'
)
YEAR
,
203
round
(sysdate,
'
month
'
)
MONTH
,
204
round
(sysdate,
'
day
'
)
DAY
from
dual
205
206
--
19,trunc[截断到最接近的日期,单位为天] ,返回的是日期类型
207
select
sysdate S1,
208
trunc(sysdate) S2,
--
返回当前日期,无时分秒
209
trunc(sysdate,
'
year
'
)
YEAR
,
--
返回当前年的1月1日,无时分秒
210
trunc(sysdate,
'
month
'
)
MONTH
,
--
返回当前月的1日,无时分秒
211
trunc(sysdate,
'
day
'
)
DAY
--
返回当前星期的星期天,无时分秒
212
from
dual
213
214
--
20,返回日期列表中最晚日期
215
select
greatest(
'
01-1月-04
'
,
'
04-1月-04
'
,
'
10-2月-04
'
)
from
dual
216
--
21.计算时间差
217
--
注:oracle时间差是以天数为单位,所以换算成年月,日
218
219
select
floor
(to_number(sysdate
-
to_date(
'
2012-11-12 15:55:03
'
,
'
yyyy-mm-dd hh24:mi:ss
'
))
/
365
)
as
spanYears
from
dual;
--
时间差-年
220
select
floor
(to_number(sysdate
-
to_date(
'
2012-11-12 15:55:03
'
,
'
yyyy-mm-dd hh24:mi:ss
'
)))
as
spanDays
from
dual;
--
时间差-天
221
select
floor
(to_number(sysdate
-
to_date(
'
2012-11-12 15:55:03
'
,
'
yyyy-mm-dd hh24:mi:ss
'
))
*
24
)
as
spanHours
from
dual;
--
时间差-时
222
select
floor
(to_number(sysdate
-
to_date(
'
2012-11-12 15:55:03
'
,
'
yyyy-mm-dd hh24:mi:ss
'
))
*
24
*
60
)
as
spanMinutes
from
dual;
--
时间差-分
223
select
floor
(to_number(sysdate
-
to_date(
'
2012-11-12 15:55:03
'
,
'
yyyy-mm-dd hh24:mi:ss
'
))
*
24
*
60
*
60
)
as
spanSeconds
from
dual;
--
时间差-秒
224
--
22.查找月的第一天,最后一天
225
SELECT
Trunc(Trunc(SYSDATE,
'
MONTH
'
)
-
1
,
'
MONTH
'
) First_Day_Last_Month,
226
Trunc(SYSDATE,
'
MONTH
'
)
-
1
/
86400
Last_Day_Last_Month,
227
Trunc(SYSDATE,
'
MONTH
'
) First_Day_Cur_Month,
228
LAST_DAY(Trunc(SYSDATE,
'
MONTH
'
))
+
1
-
1
/
86400
Last_Day_Cur_Month
229
FROM
dual;
