凯利公式指标Oracle的to_date函数

2020年10月30日发(作者：贝胤臻)

Oracle的to_date函数
日期格式参数含义说明
D一周中的星期几
DAY天的名字，使用空格填充到9个字符
DD月中的第几天
DDD年中的第几天
DY天的简写名
IW ISO标准的年中的第几周
IYYYISO标准的四位年份
YY四位年份
YYY,YY,Y年份的最后三位，两位，一位
HH小时，按12小时计
HH24小时，按24小时计
MI分
SS秒
MM月
Mon月份的简写注:
在不同的语言下显示出来的数据不同,在中文下显示为5月,在英文下显示为
MAY
Month月份的全名
W该月的第几个星期
1 18


WW年中的第几个星期
1.日期时间间隔操作
当前时间减去7分钟的时间
select sysdate,sysdate -interval ’7’ MINUTE from dual
当前时间减去7小时的时间
select sysdate -interval ’7’ hour from dual
当前时间减去7天的时间
select sysdate - interval ’7’ day from dual
当前时间减去7月的时间
select sysdate,sysdate -interval ’7’ month from dual
当前时间减去7年的时间
select sysdate,sysdate -interval ’7’ year from dual
时间间隔乘以一个数字
select sysdate,sysdate -8 *interval ’2’ hour from dual
2.日期到字符操作
select sysdate,to_char(sysdate,’yy-mm-dd hh24:mi:
ss’) from dual
select sysdate,to_char(sysdate,’yy-mm-dd hh:
mi:
ss’) from dual
select sysdate,to_char(sysdate,’yy-ddd hh:
2 18


mi:
ss’) from dual
select sysdate,to_char(sysdate,’yy-mm iw-d hh:
mi:
ss’) from dual
参考oracle的相关关文档
(ORACLE90 1DOCSERVER.901A90125SQL_ELEMENTS
4.
03.字符到日期操作
select to_date(’2003-10-17 21:15:37’,’yy-mm-dd hh24:mi:
ss’) from dual
具体用法和上面的to_char差不多。
4. trunk ROUND函数的使用
select trunc(sysdate ,’YEAR’) from dual
select trunc(sysdate ) from dual
select to_char(trunc(sysdate ,’YY’),’YY’) from dual
有毫秒级的数据类型
--返回当前时间____年__月__日小时分秒毫秒
select to_char(current_timestamp
(5),’DD- MON-YYHH24:MI:
SSxFF’) from dual;--返回当前时间的秒毫秒，可以指定秒后面的精度(最大=9)
select to_char(current_timestamp
3 18


(9),’MI:
SSxFF’) from dual;
6.计算程序运行的时间(ms)
declare
type rc is ref cursor;
l_rc rc;
l_dummy all__name%type;
l_start number default dbms__time;
begin
for I in 1 .. 1000
loop
open l_rc for
’select object_name from all_objects ’||
’where object_id = ’ || i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms__line
( round( (dbms__time- l_start)100, 2 ) ||
’ seconds...’ );
end;
4 18


========================= ========================================
====== =========
1.日期和字符转换函数用法(to_date,to_char)
to_char( to_date(222,'J'),'Jsp') from dual(这个经典....)
显示Two Hundred Twenty-Two
3.求某天是星期几select to_char(to_date('2002-08-26','yy-mm-dd'),'day') from
dual;星期一
selectto_char(to_date('2002-08-26','yy-mm-
dd'),'day','NLS_DATE_LANGUAGE=American')from dual;
monday
设置日期语言ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';1
也可以这样TO_DATE('2002-08-26','YY- mm-
dd','NLS_DATE_LANGUAGE=American')
4.两个日期间的天数select floor(sysdate - to_date('','yymmdd')) from dual;
5.时间为null的用法select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual;
注意要用TO_DATE(null)
6.a_date between to_date('','yymmdd') and to_date('','yymmdd')那么12月 31
号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
所以，当时间需要精确的时候，觉得to_char还是必要的
7.日期格式冲突问题
输入的格式要看你安装的ORACLE字符集的类型,比如:
5 18


US7ASCII,date格式的类型就是:
'01-Jan-01'
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American
或者在to_date中写selectto_char(to_date('2002-08-26' ,'yy-mm-
dd'),'day','NLS_DATE_LANGUAGE=American ')fromdual;
注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，
可查看select * from nls_session_parameters
select * from V$$NLS_PARAMETERS
8. select count(*)
from ( select rownum-1 rnum from all_objects
where rownum <= to_date('2002-02-28','yy-mm-dd') - to_date('2002-
02-01','yy-mm-dd')+1 )
where to_char( to_date('2002-02-01','yy-mm-dd')+rnum-1, 'D' )
not in ( '1', '7' )
查找2002-02-28至2002-02-01间除星期一和七的天数
在前后分别调用D BMS__TIME,让后将结果相减(得到的是1100
秒,而不是毫秒).
9. select months_between(to_date('01-31-1999','MM-DD- YY'),
to_date('12-31-1998','MM-DD-YY'))
months_between(to_date('02-01-1999','MM-DD- YY'),
to_date('12-31-1998','MM-DD-YY'))
6 18


1.
.的用法Next_day(date, day)
Monday-Sunday, for format code DAY
Mon- Sun, for format code DY
1-7, for format code
D11.
mi:
ss') TIME from all_objects
注意:
第一条记录的TIME与最后一行是一样的2可以建立一个函数来处理这个问
题create or replace function sys_date return date isbegin
return sysdate;
end;
select to_char(sys_date,'hh:
mi:
ss') from all_objects;
12.获得小时数SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16
2:38:40') fromoffer
SQL> select sysdate ,to_char(sysdate,'hh') from dual;
SYSDATE TO_CHAR(SYSDATE,'HH')
-------------------- ---------------------
2003-10-13 19:35:21 07
7 18


SQL> select sysdate ,to_char(sysdate,'hh24') from dual;
SYSDATE TO_CHAR(SYSDATE,'HH24')
-------------------- -----------------------
2003-10-13 19:35:21 19
获取____年__月__日与此类似
1
3.年__月__日的处理select older_date,
newer_date,
years,
months,
abs(
trunc(
newer_date-
add_months( older_date,years*12+months ))) days
from ( select
trunc(months_between( newer_date, older_date )12) YEARS,
mod(trunc(months_between( newer_date, older_date )),
12 ) MONTHS,
newer_date,
older_date
from ( select hiredate older_date,
8 18


add_months(hiredate,rownum)+rownum newer_date
from emp ))1
4.处理月份天数不定的办法selectto_char(ad d_months(last_day(sysdate)+1,-
2),'yymmdd'),las t_day(sysdate) from dual
15.找出今年的天数selectadd_m onths(trunc(sysdate,'year'),12)-
trunc(sysdate, 'year')fromdual3闰年的处理方法to_char( last_day( to_date('02' || :
year,'mmyy') ), 'dd' )
如果是28就不是闰年
1
6.与rr的区别
'YY99 TO_C
------- ----
yy 99 0099
rr 99 1999
yy 01 0001
rr 01
200117.不同时区的处理
se lectto_char(NEW_TIME(sysdate,'GMT','EST'),'ddmmyyh h:
mi:
ss') ,sysdate
from dual;
9 18


18.秒钟一个间隔
SelectTO_DATE(FLOOR( TO_CHAR(sysdate,'SSS')300)*300,'SSS') ,TO_CHAR(sysdate,'S
SS')
from dual
2002-11-1 9:55:00 35786
SSS表示5位秒数
1
9.一年的第几天select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
310 2002-11-6 10:03:
5120.计算小时,分,秒,毫秒select
Days,
A,
TRUNC(A*24) Hours,
TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,
TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,
TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
from(select
trunc(sysdate) Days,
sysdate - trunc(sysdate) A
from dual)select * from tabname
order by decode(mod e,'FIFO',1,-1)*to_char(rq,'yymmddhh24miss');

10 18


floor((date2-date1) 365)作为年
floor((date2-date1, 365) 30)作为月
mod(mod(date2-date1, 365), 30)作为日.
2
1.函数4next_day(sysdate,6)是从当前开始下一个星期
五。后面的数字是从星期日开始算起。
1 2 3 4 5 6 7
日一二三四五六
在oracle中有很多关于日期的函数，如:
2
2.用于从一个日期值增加或减少一些月份date_value:
=add_months(date_value,number_of_months)
例:
SQL> select add_months(sysdate,12)
Next Year
----------
13-11月-04
SQL> select add_months(sysdate,112)
Last Year
----------
13-3月-13
11 18


SQL>
23.返回当前会放时区中的当前日期date_value:
=current_date
SQL> column sessiontimezone for a15
SQL> select sessiontimezone,current_date from dual;
SESSIONTIMEZONE CURRENT_DA
--------------- ----------
+08:00 13-11月-03
SQL> alter session set time_zone='-11:00'
2
会话已更改。SQL> select sessiontimezone,current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00 12-11月-03
04.
9.
3.下午-11:
00
SQL>
24.以timestamp with time zone数据类型返回当前会 放
时区中的当前日期timestamp_with_time_zone_value:
=current_timestamp([timestamp_precision])
12 18


SQL> column sessiontimezone for a15
SQL> column current_timestamp format a36
SQL> select sessiontimezone,current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP5--------------- ---------------------------------
---
+08:00 13-11月-03
11.
6.
8.上午+08:00
SQL> alter session set time_zone='-11:00'
2
会话已更改。SQL> select sessiontimezone,current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00 12-11月-03
04.
8.
0.下午-11:00
SQL>
25.返回时区varchar_value:
=dbtimezone
SQL> select dbtimezone from dual;
13 18


DBTIME
------
-07:00
SQL>
26.找出日期或间隔值的字段值date_value:
=extract(date_f ieldfrom[datetime_value|interval_value])
SQL> select extract(month from sysdate)
This Month
----------
11
SQL> select extract(year from add_months(sysdate,36))
3 Years Out
-----------
2006
SQL>
27.返回包含了日期参数的月份的最后一天的日期date_value:
=last_day(date_value)SQL> select last_day(date'2000-02-01')
dual;
Leap Yr?
----------
29-2月-00
SQL> select last_day(sysdate)
14 18


31.给定一 个日期值，返回由第二个参数指出的日子第一次出现
在的日期值(应返回相应日子的名称字符串)
3
2.查询某周的第一天
selecttrunc(decode(ww,53,t o_date(yy||'3112','yyddmm'),to_date(yy || '-' ||
to_char(ww * 7), 'yy-ddd')), 'd') last_day
from (select substr('2004-32', 1, 4) yy, to_number(substr('2004-32', 6)) ww
from dual)
select
trunc(to_date(substr('2003-01',1,5) ||to_char((to_number(substr('2003-
01',6)))*7), 'yy-ddd'),'d')-6first_day from dual
select min(v_date) from
(select (to_date('200201','yymm') + rownum) v_date
from all_tables
where rownum < 370)
where to_char(v_date,'yy-iw') = '2002-49'
33.查询某周的最后一天
selecttrunc(decode(ww,53,to_date(y y||'3112','yyddmm'),to_date(yy || '-' ||
to_char(ww * 7), 'yy-ddd')), 'd') - 6 first_day
from (select substr('2004-33', 1, 4) yy, to_number(substr('2004-33', 6)) ww
from dual)
selecttrunc(to_date(substr('2003-01',1,5 )||to_char((to_number(substr('2003-
01',6)))*7) ,'yy-ddd'),'d')last_day from dual
select max(v_date) from
15 18


(select (to_date('200408','yymm') + rownum) v_date
from all_tables
where rownum < 370)
where to_char(v_date,'yy-iw') = '2004-33'
34.查询某周的日期select min_date, to_char(min_date,'day') day from
(select to_da te(substr('2004-33',1,4)||'001'+rownum-1,'yyddd') min_date
from all_tables
where rownum <= d ecode(mod(to_number(substr('2004-33',1,4)),4),0,36 6,365)
union
selectto_date(substr('2004-33 ',1,4)-1||decode(mod(to_number(substr('2004-
33 ',1,4))-1,4),0,359,358)+rownum,'yyddd') min_date
from all_tables
where rownum <= 7
union
select to_date(substr('2004-33',1,4) +1||'001'+rownum-1,'yyddd') min_date
from all_tables8where rownum <= 7)where to_char(min_date,'yy-iw') ='2004-
33'
35.中时间运算的问题
内容如下:
1、oracle支持对日期进行运算
2、日期运算时是以天为单位进行的
3、当需要以分秒等更小的单位算值时，按时间进制进行转换即可
16 18


4、进行时间进制转换时注意加括号(见示例中红色括号)，否则会出问题
SQL> alter session set nls_date_format='yy-mm- dd hh:
mi:
ss';
会话已更改。
SQL> set serverout on
SQL> declare
2 DateValue date;
3 begin
4 select sysdate into DateValue from dual;
5 dbms__line('源时间:
'||to_char(DateValue));
6 dbms__line('源时间减1天:
'||to_char(DateValue-1));
7 dbms__line('源时间减1天1小时:
'||to_char(DateValue-1-124));
8 dbms__line('源时间减1天1小时1分:
'||to_char(DateValue- 1-124-1(24*60)));9dbms__line('源时间减1
天1小时1分秒: '||to_char(DateValue-1-124-1(24*60)-1(24*60*60)) );
10 end;
17 18


11
源时间:2003-12-29 11:53:41
源时间减1天:2003-12-28 11:53:41
源时间减1天1小时:2003-12-28 10:53:41
源时间减1天1小时1分:2003-12-28 10:52:41
源时间减1天1小时1分1秒:2003-12-28 10:52:40
PLSQL过程已成功完成。
SQL>
91
18 18