前言 设置日期语言

 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两位年显示21
yyythree digits三位年显示021
yyyyfour digits四位年显示2021
月(Month)mmnumber两位月显示06
monabbreviated字符集表示显示6月(若是英文版,显示Jun)
monthspelled out字符集表示显示6月(若是英文版,显示June)
日(Day)ddnumber当月第几天显示30
dddnumber当年第几天显示181
dyabbreviated当周第几天缩写显示星期三,若是英文版显示Wed
dayspelled out当周第几天全写显示星期三,若是英语显示wednesday
dnumber当周第几天,返回数字显示4, 每周第1天是星期天
ddspthspelled out当月第几天(英文显示)显示thirtieth
Hour(时)hhtwo digits12小时制显示03
hh24two digits 24小时制显示15
Minute(分)mitwo digits60进制显示36
Second(秒)sstwo digits60进制显示23
Quarter(季度)Qdigit季度显示2
当年第几周WWdigit当年第几周(从年的第一天算7天为一周)显示26
IWdigit年的自然周显示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)

Logo

华为云1024程序员节送福利,参与活动赢单人4000元礼包,更有热门技术干货免费学习

更多推荐