Oracle 日期函数大全

oracle 日期函数网上已经有了不少，特我们跟集中一下，免得大家麻烦。

在oracle数据库的开发中，常因为时间的问题大费周章，所以特地将ORACLE数据的日期函数收藏致此。乃供他日所查也。

1、add_months(d,n) 日期d加n个月

SQL> SELECT SYSDATE AS This_Day,add_months(SYSDATE,1) AS Next_Day FROM dual;

THIS_DAY NEXT_DAY

-------------- ------------

08-9月 -10 08-10月-10

2、last_day(d) 包含d的月份的最后一天的日期

SQL> select last_day(sysdate) as last_day from dual;

LAST_DAY

-----------

30-9月 -10

3、new_time(d,a,b) 时区的日期和时间d在b时区的日期和时间

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') as china,

to_char(new_time(sysdate,'est','GMT'),'YYYY-MM-DD HH24:MI:SS') as GMT

from dual;

CHINA GMT

------------------- -------------------

2010-09-08 09:51:50 2010-09-08 14:51:50

4、next_day(d,day) 比日期d晚，由day指定的周几的日期

SQL> select sysdate as this_day, next_day(sysdate,7) as next_sat from dual;

THIS_DAY NEXT_SAT

-------------- --------------

08-9月 -10 11-9月 -10

5、sysdate 当前的系统日期和时间

6、greatest(d1,d2,...dn) 给出的日期列表中最后的日期

SQL> select sysdate as this_day,greatest(sysdate,sysdate+1,sysdate+2) as max_day from dual;

THIS_DAY MAX_DAY

-------------- --------------

08-9月 -10 10-9月 -10

7、least(d1,k2,...dn) 给出的日期列表中最早的日期

SQL> select sysdate as this_day,least(sysdate,sysdate+1,sysdate-1) as min_day from dual;

THIS_DAY MIN_DAY

-------------- --------------

08-9月 -10 07-9月 -10

8、to_char(d [,fmt]) 日期d按fmt指定的格式转变成字符串

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

TODAY

-------------------

2010-09-08 10:37:08

9、to_date(st [,fmt]) 字符串st按fmt指定的格式转成日期值，若fmt忽略，st要用缺省格式

SQL> select to_date('2010-09-08 10:37:08','YYYY-MM-DD HH24:MI:SS') as today from dual;

TODAY

--------------

08-9月 -10

10、round(d [,fmt]) 日期d按fmt指定格式舍入到最近的日期

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') as this

,round(sysdate) as round_day

,to_char(sysdate+1/6,'YYYY-MM-DD HH24:MI:SS') as next_4hour

,round(sysdate+1/6) as round_day

from dual;

THIS ROUND_DAY NEXT_4HOUR ROUND_DAY

------------------- -------------- ------------------- --------------

2010-09-08 10:52:45 08-9月 -10 2010-09-08 14:52:45 09-9月 -10

11、trunc(d [,fmt]) 日期d按fmt指定格式截断到最近的日期

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') as this

,trunc(sysdate) as trunc_day

,to_char(sysdate+1/6,'YYYY-MM-DD HH24:MI:SS') as next_4hour

,trunc(sysdate+1/6) as trunc_day

from dual;

THIS TRUNC_DAY NEXT_4HOUR TRUNC_DAY

------------------- -------------- ------------------- --------------

2010-09-08 10:56:18 08-9月 -10 2010-09-08 14:56:18 08-9月 -10

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 ....

日期和字符转换函数用法（to_date,to_char）

select to_char( to_date(222,'J'),'Jsp') from dual

显示Two Hundred Twenty-Two

求某天是星期几

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;

星期一

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'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')

两个日期间的天数

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)

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','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from

dual;

注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，

可查看

select * from nls_session_parameters

select * from V$NLS_PARAMETERS

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秒, 而不是毫秒).

select months_between(to_date('01-31-1999','MM-DD-YYYY'),

to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;

select months_between(to_date('02-01-1999','MM-DD-YYYY'),

to_date('12-31-1998','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

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 ,to_char(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,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,

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,

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(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),instr((time2-time1),' ')+4,2)　　　　　　　　 minutes,

　　　5　　　　 substr((time2-time1),instr((time2-time1),' ')+1,2)　　　　　　　　 hours,

　　　6　　　　 trunc(to_number(substr((time2-time1),1,instr(time2-time1,' '))))　 days,

　　　7　　　　 trunc(to_number(substr((time2-time1),1,instr(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中改写?

Oracle 日期函数大全

继续阅读

Oracle的基本操作

【JAVASE学习笔记之操作时间跟日期的类】：Date类，DateFormat类，Calendar类Date类：

java 日期总结

为什么要选择UniDAC

新鲜传媒CEO纪中展：经济危机对年轻人是机会新鲜传媒CEO纪中展：经济危机对年轻人是机会纪中展：加入创业团队初期不要考虑名利纪中展：创业者骨子里要有霸气纪中展：创业是颠覆过去的过程

SIP Presence (二)RFC 3265 - Session Initiation Protocol (SIP)-Specific Event Notification

SQL优化SQL语句优化的目的

JAVA高效编程指南

坐标系统和投影变换在桌面产品中的应用

windows不能在本地计算机上运行oracleDbConsoleorcl

PHP进阶学习之session写入数据库

关于SQL语言

SQL语言基础：常用的数据查询语句

neo4j之cypher使用文档

sqlServer根据经纬查距离

Oracle 批量查询传入List 返回List