oracle 日期函数集合(集中版本)

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了oracle 日期函数集合(集中版本)，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

在oracle数据库的开发中，常因为时间的问题大费周章，所以特地将ORACLE数据的日期函数收藏致此。乃供他日所查也。
add_months(d,n) 日期d加n个月
last_day(d) 包含d的月?的最后一天的日期
new_time(d,a,b) a?区的日期和??d在b?区的日期和??
next_day(d,day) 比日期d?，由day指定的周几的日期
sysdate 当前的系?日期和??
greatest(d1,d2,...dn) ?出的日期列表中最后的日期
least(d1,k2,...dn) ?出的日期列表中最早的日期
to_char(d [,fmt]) 日期d按fmt指定的格式??成字符串
to_date(st [,fmt]) 字符串st按fmt指定的格式?成日期?，若fmt忽略，st要用缺省格式
round(d [,fmt]) 日期d按fmt指定格式舍入到最近的日期
trunc(d [,fmt]) 日期d按fmt指定格式截断到最近的日期
to_date 字符串类型转为换日期类型
字符串中的相应位置上的字符,必须符合时间范围的限制 查询Oracle日期格式
----------------------------------
select from nls_database_parameters;
得到结果如下表: 表中NLS_DATE_FORMAT表示日期格式.
PARAMETER VALUE
----------------------------------- -----------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_NCHAR_CHARACTERSET ZHS16GBK
NLS_RDBMS_VERSION 8.1.7.0.0 或者查询V$NLS_PARAMETERS表,
select from V$NLS_PARAMETERS;
也有类似结果 sql>select to_date('2004-11-12 12-07-32','yyyy-mm-dd hh24-mi-ss') value from dual;
VALUE
-------------------
2004.11.12 12:07:32 sql>select to_date('20041015') value from dual;
VALUE
-------------------
2004.10.15 00:00:00 sql>select to_date('20041315') value from dual;
ERROR 位于第 1 行:
ORA-01861: 文字与格式字符串不匹配
sysdate 当前日期和时间 sql>select sysdate value from dual;
VALUE
-------------------
2003.11.23 17:09:01
last_day 本月最后一天 sql>select last_day(sysdate) value from dual;
VALUE
-------------------
2003.11.30 17:08:17
add_months(d,n) 日期d后推n个月 sql>select add_months(sysdate,2) value from dual;
VALUE
-------------------
2005.01.23 17:10:21
next_day(d,day) 日期d之后的第一周中,指定的那天(指定星期的第几天)是什么日期 sql>select next_day(sysdate,1) value from dual;
VALUE
-------------------
2004.11.28 17:38:55
[oracle/plsql]oracle日期处理完全版 日期处理完全版
TO_DATE格式
Day:
dd number 12
dy abbreviated fri
day spelled out friday
ddspth spelled out,ordinal twelfth
Month:
mm number 03
mon abbreviated mar
month spelled out march
Year:
yy two digits 98
yyyy four digits 1998 24小时格式下时间范围为： 0:00:00 - 23:59:59....
12小时格式下时间范围为： 1:00:00 - 12:59:59 ....
1.
日期和字符转换函数用法（to_date,to_char） 2.
select to_char( to_date(222,'J'),'Jsp') from dual 显示Two Hundred Twenty-Two 3.
求某天是星期几
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
星期一
select to_char(to_date('2002-08-26','day','NLS_DATE_LANGUAGE = American') from dual;
monday
设置日期语言
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
也可以这样
TO_DATE ('2002-08-26','YYYY-mm-dd','NLS_DATE_LANGUAGE = American') 4.
两个日期间的天数
select floor(sysdate - to_date('20020405','yyyymmdd')) 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('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')
那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
所以，当时间需要精确的时候，觉得to_char还是必要的
7. 日期格式冲突问题
输入的格式要看你安装的ORACLE字符集的类型,比如: US7ASCII,date格式的类型就是: '01-Jan-01'
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American
或者在to_date中写
select to_char(to_date('2002-08-26','NLS_DATE_LANGUAGE = American') from dual;
注意我这只是举了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','yyyy-mm-dd') - to_date('2002-
02-01','yyyy-mm-dd')+1
)
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1,'D' )
not
in ( '1','7' ) 查找2002-02-28至2002-02-01间除星期一和七的天数
在前后分别调用DBMS_UTILITY.GET_TIME,让后将结果相减(得到的是1/100秒,而不是毫秒). 9.
select months_between(to_date('01-31-1999','MM-DD-YYYY'),
to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1 select months_between(to_date('02-01-1999','MM-DD-YYYY')) "MONTHS" FROM DUAL; 1.03225806451613
10. Next_day的用法
Next_day(date,day) Monday-Sunday,for format code DAY
Mon-Sun,for format code DY
1-7,for format code D 11
select to_char(sysdate,'hh:mi:ss') TIME from all_objects
注意：第一条记录的TIME 与最后一行是一样的
可以建立一个函数来处理这个问题
create or replace function sys_date return date is
begin
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') from offer
sql> select sysdate,to_char(sysdate,'hh') from dual; SYSDATE TO_CHAR(SYSDATE,'HH')
-------------------- ---------------------
2003-10-13 19:35:21 07 sql> select sysdate,'hh24') from dual; SYSDATE TO_CHAR(SYSDATE,'HH24')
-------------------- -----------------------
2003-10-13 19:35:21 19 获取年月日与此类似
13.
年月日的处理
select older_date,
newer_date,
years,
months,
abs(
trunc(
newer_date-
add_months( older_date,years12+months )
)
) days
from ( select
trunc(months_between( newer_date,older_date )/12) YEARS,
mod(trunc(months_between( newer_date,older_date )),
12 ) MONTHS,
older_date
from ( select hiredate older_date,
add_months(hiredate,rownum)+rownum newer_date
from emp )
) 14.
处理月份天数不定的办法
select to_char(add_months(last_day(sysdate) +1,-2),'yyyymmdd'),last_day(sysdate) from dual 16.
找出今年的天数
select add_months(trunc(sysdate,'year'),12) - trunc(sysdate,'year') from dual 闰年的处理方法
to_char( last_day( to_date('02' || :year,'mmyyyy') ),'dd' )
如果是28就不是闰年 17.
yyyy与rrrr的区别
'YYYY99 TO_C
------- ----
yyyy 99 0099
rrrr 99 1999
yyyy 01 0001
rrrr 01 2001 18.不同时区的处理
select to_char( NEW_TIME( sysdate,'GMT','EST'),'dd/mm/yyyy hh:mi:ss'),sysdate
from dual; 19.
5秒钟一个间隔
Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) 300,'SSSSS'),TO_CHAR(sysdate,'SSSSS')
from dual 2002-11-1 9:55:00 35786
SSSSS表示5位秒数 20.
一年的第几天
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
310 2002-11-6 10:03:51 21.计算小时,分,秒,毫秒
select
Days,
A,
TRUNC(A24) Hours,
TRUNC(A2460 - 60TRUNC(A24)) Minutes,
TRUNC(A246060 - 60TRUNC(A2460)) Seconds,
TRUNC(A246060100 - 100TRUNC(A246060)) mSeconds
from
(
select
trunc(sysdate) Days,
sysdate - trunc(sysdate) A
from dual
)
select from tabname
order by decode(mode,'FIFO',1,-1)to_char(rq,'yyyymmddhh24miss'); //
floor((date2-date1) /365) 作为年
floor((date2-date1,365) /30) 作为月
mod(mod(date2-date1,365),30)作为日.
23.next_day函数
next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。 很简单的一句话。
把Oracle的日期当作一个特殊数字，以天为单位。
可以进行 日期+数字 = 日期， 日期-日期=数字，日期-数字=日期 MESTAMP数据的格式化显示和DATE 数据一样。注意，to_char函数支持date和timestamp，但是trunc却不支持TIMESTAMP数据类型。这已经清楚表明了在当两个时间的差别极度重要的情况下，使用TIMESTAMP数据类型要比DATE数据类型更确切。
　　
　　如果你想显示TIMESTAMP的小数秒信息，参考下面：
　　
　　1　SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date" FROM date_table
　　
　　Date
　　
　　-----------------------
　　
　　06/20/2003 16:55:14:000
　　
　　06/26/2003 11:16:36:000
　　
　　在上例中，我只现实了小数点后3位的内容。
　　
　　计算timestamp间的数据差别要比老的date数据类型更容易。当你直接相减的话，看看会发生什么。结果将更容易理解，第一行的17天，18小时，27分钟和43秒。
　　
　　1　SELECT time1,
　　
　　　2　　　　 time2,
　　
　　　3　　　　 substr((time2-time1),instr((time2-time1),' ')+7,2)　　　　　　　　 seconds,
　　
　　　4　　　　 substr((time2-time1),' ')+4,2)　　　　　　　　 minutes,
　　
　　　5　　　　 substr((time2-time1),' ')+1,2)　　　　　　　　 hours,
　　
　　　6　　　　 trunc(to_number(substr((time2-time1),instr(time2-time1,' '))))　 days,
　　
　　　7　　　　 trunc(to_number(substr((time2-time1),' ')))/7) weeks
　　
　　　8*　 FROM date_table
　　
　　TIME1　　　　　　　　　　　 TIME2　　　　　　　　　　　SECONDS MINUTES HOURS DAYS WEEKS
　　
　　-------------------------　 -------------------------- ------- ------- ----- ---- -----
　　
　　06/20/2003:16:55:14:000000　07/08/2003:11:22:57:000000　43　　 27　　　18　　17　　2
　　
　　06/26/2003:11:16:36:000000　07/08/2003:11:22:57:000000　21　　 06　　　00　　12　　1
　　
　　这就意味着不再需要关心一天有多少秒在麻烦的计算中。因此，得到天数、月数、天数、时数、分钟数和秒数就成为用substr函数摘取出数字的事情了。
　　
　　系统日期和时间
　　
　　为了得到系统时间，返回成date数据类型。你可以使用sysdate函数。
　　
　　sql> SELECT SYSDATE FROM DUAL;
　　
　　为了得到系统时间，返回成timestamp数据类型。你可以使用systimpstamp函数。
　　
　　sql> SELECT SYSTIMESTAMP FROM DUAL;
　　
　　你可以设置初始化参数FIXED_DATE指定sysdate函数返回一个固定值。这用在测试日期和时间敏感的代码。注意，这个参数对于systimestamp函数无效。
　　
　　sql> ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00';
　　
　　System altered.
　　
　　sql> select sysdate from dual;
　　
　　SYSDATE
　　
　　---------
　　
　　01-JAN-03
　　
　　sql> select systimestamp from dual;
　　
　　SYSTIMESTAMP
　　
　　---------------------------------------------------------
　　
　　09-JUL-03 11.05.02.519000 AM -06:00
　　
　　当使用date和timestamp类型的时候，选择是很清楚的。你可以随意处置date和timestamp类型。当你试图转换到更强大的timestamp的时候，需要注意，它们既有类似的地方，更有不同的地方，而足以造成破坏。两者在简洁和间隔尺寸方面各有优势，请合理地选择。
DateDiff( month,waterpay.CopyDate,GetDate() ) = 1 ) DateDiff( day,WaterPay.CopyDate,GetDate() ) = 1 )
这两个句子的意思分别是：求以month或者以day的方式返回当前日期（getdate）减数据库中的一个日期字段（waterpay.CopyDate）的值等于1的记录
请问这两句在sql server中的函数如何在oracle中改写? 问题点数：20、回复次数：2
Top
1 楼hevin（没有什么是不可能的）回复于 2005-09-25 18:12:10 得分 0 第一句：
Months_Between( to_date(to_char(sysdate,
to_date(to_char(waterpay.CopyDate,'yyyy-mm-dd')
) = 1
Top 2 楼hevin（没有什么是不可能的）回复于 2005-09-25 18:18:06 得分 20第二句：
to_char(sysdate - 1,'yyyy-mm-dd') = to_char(waterpay.CopyDate,'yyyy-mm-dd')一、 常用日期数据格式 1.Y或YY或YYY 年的最后一位，两位或三位
sql> Select to_char(sysdate,'Y') from dual;
TO_CHAR(SYSDATE,'Y')
--------------------
7
sql> Select to_char(sysdate,'YY') from dual;
TO_CHAR(SYSDATE,'YY')
---------------------
07
sql> Select to_char(sysdate,'YYY') from dual;
TO_CHAR(SYSDATE,'YYY')
----------------------
007
2.Q 季度 1～3月为第一季度，2表示第二季度。
sql> Select to_char(sysdate,'Q') from dual;
TO_CHAR(SYSDATE,'Q')
--------------------
2
3.MM 月份数
sql> Select to_char(sysdate,'MM') from dual;
TO_CHAR(SYSDATE,'MM')
---------------------
05
4.RM 月份的罗马表示 （V在罗马数字中表示 5）
sql> Select to_char(sysdate,'RM') from dual;
TO_CHAR(SYSDATE,'RM')
---------------------
V
5.Month 用9个字符长度表示的月份名
sql> Select to_char(sysdate,'Month') from dual;
TO_CHAR(SYSDATE,'MONTH')
------------------------
5月
6.WW 当年第几周 （2007年5月29日为2007年第22周）
sql> Select to_char(sysdate,'WW') from dual;
TO_CHAR(SYSDATE,'WW')
---------------------
22
7.W 本月第几周 （2007年5月29日为5月第5周）
sql> Select to_char(sysdate,'W') from dual;
TO_CHAR(SYSDATE,'W')
--------------------
5
8.DDD 当年第几天 （2007年5月29日为2007年第149天）
sql> Select to_char(sysdate,'DDD') from dual;
TO_CHAR(SYSDATE,'DDD')
----------------------
149
9. DD 当月第几天
sql> Select to_char(sysdate,'DD') from dual;
TO_CHAR(SYSDATE,'DD')
---------------------
29
10.D 周内第几天
sql> Select to_char(sysdate,'D') from dual;
TO_CHAR(SYSDATE,'D')
--------------------
3
11.DY 中文的星期几 （ （2007年5月29日为星期二））
sql> Select to_char(sysdate,'DY') from dual;
TO_CHAR(SYSDATE,'DY')
---------------------
星期二
12.HH或HH12 12进制小时数 （16：09分为用12小时制计时为4点）
sql> Select to_char(sysdate,'HH') from dual;
TO_CHAR(SYSDATE,'HH')
---------------------
04
13.HH24 24小时制
sql> Select to_char(sysdate,'HH24') from dual;
TO_CHAR(SYSDATE,'HH24')
-----------------------
16
二、常用时间函数
1.trunc(sysdate,'Q') 本季度第一天
sql> select trunc(sysdate,'Q') from dual;
TRUNC(SYSDATE,'Q')
------------------
2007-4-1
2.trunc(sysdate,'D') 本周的第一天(周日)
sql> select trunc(sysdate,'D')from dual;
TRUNC(SYSDATE,'D')
------------------
2007-5-27
3.last_day(sysdate) 本月最后一天
sql> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-----------------
2007-5-31 15:20:3
4.add_months(sysdate,2) 日期sysdate后推2个月 sql> select add_months(sysdate,2) from dual;
ADD_MONTHS(SYSDATE,2)
---------------------
2007-7-29 15:21:14
5.next_day(sysdate,2) 日期sysdate之后的第一周中,第2(指定星期的第几天)是什么日期
sql> select next_day(sysdate,2) from dual;
NEXT_DAY(SYSDATE,2)
-------------------
2007-6-4 15:22:10
6.Months_between(f,s) 日期f和s间相差月数
sql> select months_between(sysdate,to_date('2007-04-12','yyyy-mm-dd'))from dual;
MONTHS_BETWEEN(SYSDATE,TO_DATE
------------------------------
1.56909908900836 7.得到SYSDATE+5所在的月份
sql> SELECT to_char(SYSDATE+5,'mon','nls_date_language=american') FROM dual;
TO_CHAR(SYSDATE+5,'MON','NLS_D
------------------------------
jun
8.current_date()返回当前会话时区中的当前日期 。
9.select dbtimezone from dual;
10.extract()找出日期或间隔值的字段值
sql> select extract(month from sysdate) "This Month" from dual;
This Month
----------
5
sql> select extract(year from sysdate) "This year" from dual;
This year
----------
2007
sql> select extract(month from add_months(sysdate,2)) " Month" from dual;
Month
----------
7 ==================================================================
三、一些实践后的用法：
1.上月末天：
select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from dual；
2.上月今天
sql> select to_char(add_months(sysdate,'yyyy-MM-dd') PreToday from dual;
3.上月首天
sql> select to_char(add_months(last_day(sysdate)+1,'yyyy-MM-dd') firstDay from dual;
4.要找到某月中所有周五的具体日期
SELECT to_char(b.a,'YY-MM-DD')
FROM ( SELECT trunc(SYSDATE,'mm')+ROWNUM-1 a
FROM dba_objects where rownum < 32 ) b
WHERE to_char(b.a,'day')='星期五'; 如果把where to_char(t.d,'MM') = to_char(sysdate,'MM')改成sysdate-90，即为查找当前月份的前三个月中
的每周五的日期。 5.得到系统当前月及以后的日期
select trunc(sysdate,'MM')+ROWNUM-1 FROM dba_objects ; -----------------------------------
to_date 字符串类型转为换日期类型
字符串中的相应位置上的字符,必须符合时间范围的限制
14.MI 分钟数(0～59)
提示注意不要将MM格式用于分钟(分钟应该使用MI)。MM是用于月份的格式，将它用于分钟也能工作，但结果是错误的。
15.SS 秒数(0～59)

oracleoracle日期函数

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问