Oracle 日期时间函数大全_焦糖花粥_oracle 日期函数

网络投稿 02-07 5956

前言设置日期语言

alter session set NLS_DATE_LANGUAGE='AMERICAN'; alter session set NLS_DATE_LANGUAGE='SIMPLIFIED CHINESE'; --也可以这样写： to_date('2021-06-30 15:23:45','yyyy-mm-dd hh24:mi:ss',NLS_DATE_LANGUAGE='AMERICAN' )

可通过如下语句查看：

select * from nls_session_parameters select * from V$NLS_PARAMETERS 一、日期格式

以 2021-06-30 15:36:23 为例?

格式类型名称示例年（Year）yytwo digits两位年显示21yyythree digits三位年显示021yyyyfour digits四位年显示2021月(Month)mmnumber两位月显示06monabbreviated字符集表示显示6月(若是英文版，显示Jun)monthspelled out字符集表示显示6月(若是英文版，显示June)日（Day）ddnumber当月第几天显示30dddnumber当年第几天显示181dyabbreviated当周第几天缩写显示星期三，若是英文版显示Weddayspelled out当周第几天全写显示星期三，若是英语显示wednesdaydnumber当周第几天，返回数字显示4, 每周第1天是星期天ddspthspelled out当月第几天（英文显示）显示thirtiethHour(时)hhtwo digits12小时制显示03hh24two digits?24小时制显示15Minute(分）mitwo digits60进制显示36Second（秒）sstwo digits60进制显示23Quarter(季度）Qdigit季度显示2当年第几周WWdigit当年第几周（从年的第一天算7天为一周）显示26IWdigit年的自然周显示26当月第几周Wdigit当月第几周显示5

注意：

1. WW 是从年的第一天算7天为一周，IW是自然周算的，可以看如下代码：

select to_char(to_date('2020-01-06','yyyy-mm-dd'),'IW') from dual --02 select to_char(to_date('2020-01-06','yyyy-mm-dd'),'WW') from dual --01

? ? ? ?24小时格式下时间范围为： 0:00:00 - 23:59:59....?????? ??????? 12小时格式下时间范围为： 1:00:00 - 12:59:59 ....?

2. 一周内的第几天：‘D’ 没星期的第1天是‘星期日’?

二、日期类型和字符类型转换函数 to_date,to_char? select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期转化为字符串 select to_char(sysdate,'yyyy') as nowYear from dual; //获取时间的年 select to_char(sysdate,'mm') as nowMonth from dual; //获取时间的月 select to_char(sysdate,'dd') as nowDay from dual; //获取时间的日 select to_char(sysdate,'hh24') as nowHour from dual; //获取时间的时 select to_char(sysdate,'mi') as nowMinute from dual; //获取时间的分 select to_char(sysdate,'ss') as nowSecond from dual; //获取时间的秒

但是呢，to_date就要求转换的格式要和输入的字符型日期要对应，不然会报错,如下：

这个呢是因为少了时分秒了。

?时间为null的用法 to_date(null)?

NEXT_DAY(X,Y)：用于计算X时间后第一个星期Y的时间。Y是一个字符串，表示用当前会话语言表示的一周中某一天的全称（如星期一、星期二等），也可以是数值。

TRUNC(X [,FORMAT])：截断日期，返回的是日期,?FORMAT?中与周相关的有D，IW，WW，W，FMWW

--取周的开始时间和结束时间 SELECT TRUNC(TO_DATE('2021-06-30','YYYY-MM-DD'),'IW') AS STARTDATE FROM DUAL;--本周周一 SELECT TRUNC(TO_DATE('2021-06-30','YYYY-MM-DD'),'IW') + 6 AS ENDDATE FROM DUAL;--本周周日 SELECT TRUNC(TO_DATE('2021-06-30','YYYY-MM-DD'),'IW') - 7 AS STARTDATE FROM DUAL;---上周周一 SELECT TRUNC(TO_DATE('2021-06-30','YYYY-MM-DD'),'IW') - 1 AS ENDDATE FROM DUAL;--上周周日

EXTRACT():从一个date或者interval类型中截取到特定的部分，

extract ( { year | month | day | hour | minute | second } | { timezone_hour | timezone_minute } | { timezone_region | timezone_abbr } from { date_value | interval_value } )

oracle中extract()函数----用于截取年、月、日、时、分、秒 - 小强斋太 - 博客园 (cnblogs.com)

select extract (year from sysdate) year, extract (month from sysdate) month, extract (day from sysdate) day from dual; select extract (year from date '2015-05-04') year, extract (month from date'2015-05-04') month, extract (day from date '2011-05-04') day from dual;

?从timestamp中获取年月日时分秒

select extract(year from systimestamp) year ,extract(month from systimestamp) month ,extract(day from systimestamp) day ,extract(minute from systimestamp) minute ,extract(second from systimestamp) second ,extract(timezone_hour from systimestamp) th ,extract(timezone_minute from systimestamp) tm ,extract(timezone_region from systimestamp) tr ,extract(timezone_abbr from systimestamp) ta from dual

三、时间差 ?3.1 年份差（相差月数/12） select ((months_between(TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'),TO_DATE('2016-5-31','yyyy-mm-dd hh24:mi:ss')))/12) As 相差年份 from dual; --结果:2 select trunc(months_between(sysdate, to_date('2017-01-01','yyyy-mm-dd')) / 12) from dual; --结果：4 SELECT EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE('2017-01-01','YYYY-MM-DD')) YEARS FROM DUAL; --结果:4 3.2 月数差（months_between()函数） --oracle两个日期的相差月数-- --1）月份都是最后一天，A日期 > B日期 ,返回整数 --- select months_between(TO_DATE('2018-6-30','yyyy-mm-dd hh24:mi:ss'),TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss')) As 相差月份1 from dual; --返结果：1 --2）月份都是最后一天，B日期 > A日期 ,返回负数 --- select months_between(TO_DATE('2018-4-30','yyyy-mm-dd hh24:mi:ss'),TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss')) As 相差月份2 from dual; --f返回结果：-1 --3）月份天数不一样，A日期 > B日期 ,返回带小数的数字--- select months_between(TO_DATE('2018-6-25','yyyy-mm-dd hh24:mi:ss'),TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss')) As 相差月份3 from dual; --返回结果：0.8064516... 3.3 相差天数（两个日期相减，并用to_number()函数） --Oracle中两个日期相差天数-- select TO_NUMBER(TO_DATE('2021-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss')) AS 相差天数 from dual; 3.4 相差小时数，分钟数，秒数（时制进行转换） --Oracle中两个日期相差小时数-- select TO_NUMBER((TO_DATE('2018-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))*24) AS 相差小时数 from dual; --Oracle中两个日期相差分钟数-- select TO_NUMBER((TO_DATE('2018-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))*24*60) AS 相差分钟数 from dual; --Oracle中两个日期相差秒数-- select TO_NUMBER((TO_DATE('2018-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))*24*60*60) AS 相差秒数 from dual; 3.5 日期加法

?sysdate+1 加一天 sysdate+1/24 加1小时 sysdate+1/(24*60) 加1分钟 sysdate+1/(24*60*60) 加1秒钟类推至毫秒0.001秒

加法? select sysdate,add_months(sysdate,12) from dual; ? ? ? ?--加1年? select sysdate,add_months(sysdate,1) from dual; ? ? ? ?--加1月? select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual; ?--加1星期? select sysdate,to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual; ?--加1天? select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual; ?--加1小时? select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; ?--加1分钟? select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; ?--加1秒? 减法? select sysdate,add_months(sysdate,-12) from dual; ? ? ? ?--减1年? select sysdate,add_months(sysdate,-1) from dual; ? ? ? ?--减1月? select sysdate,to_char(sysdate-7,'yyyy-mm-dd HH24:MI:SS') from dual; ?--减1星期? select sysdate,to_char(sysdate-1,'yyyy-mm-dd HH24:MI:SS') from dual; ?--减1天? select sysdate,to_char(sysdate-1/24,'yyyy-mm-dd HH24:MI:SS') from dual; ?--减1小时? select sysdate,to_char(sysdate-1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; ?--减1分钟? select sysdate,to_char(sysdate-1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; ?--减1秒 ?

3.6 获取两个日期之间的时间间隔，extract(）函数是最好的选择 select extract (day from dt2 - dt1) day, extract (hour from dt2 - dt1) hour, extract (minute from dt2 - dt1) minute, extract (second from dt2 - dt1) second from ( select to_timestamp ('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1, to_timestamp ('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2 from dual ) 3.7 获取日期中最晚的一个 select greatest('01-1月-04','04-1月-04','10-2月-04') from dual 3.8 查找月的第一天，最后一天 SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month, Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month, Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month, LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month FROM dual;

部分内容参照??ORACLE日期时间函数大全 - 阿郎 - 博客园 (cnblogs.com)