Oracle 日期时间函数大全
前言 设置日期语言alter session set NLS_DATE_LANGUAGE='AMERICAN';--也可以这样写:to_date('2021-06-30 15:23:45','yyyy-mm-dd hh24:mi:ss',NLS_DATE_LANGUAGE='AMERICAN' )一、日期格式以 2021-06-30 15:36:23 为例格式类型名称示例年(Year)yytwo
前言 设置日期语言
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) | yy | two digits | 两位年 | 显示21 |
yyy | three digits | 三位年 | 显示021 | |
yyyy | four digits | 四位年 | 显示2021 | |
月(Month) | mm | number | 两位月 | 显示06 |
mon | abbreviated | 字符集表示 | 显示6月(若是英文版,显示Jun) | |
month | spelled out | 字符集表示 | 显示6月(若是英文版,显示June) | |
日(Day) | dd | number | 当月第几天 | 显示30 |
ddd | number | 当年第几天 | 显示181 | |
dy | abbreviated | 当周第几天缩写 | 显示星期三,若是英文版显示Wed | |
day | spelled out | 当周第几天全写 | 显示星期三,若是英语显示wednesday | |
d | number | 当周第几天,返回数字 | 显示4, 每周第1天是星期天 | |
ddspth | spelled out | 当月第几天(英文显示) | 显示thirtieth | |
Hour(时) | hh | two digits | 12小时制 | 显示03 |
hh24 | two digits | 24小时制 | 显示15 | |
Minute(分) | mi | two digits | 60进制 | 显示36 |
Second(秒) | ss | two digits | 60进制 | 显示23 |
Quarter(季度) | Q | digit | 季度 | 显示2 |
当年第几周 | WW | digit | 当年第几周(从年的第一天算7天为一周) | 显示26 |
IW | digit | 年的自然周 | 显示26 | |
当月第几周 | W | digit | 当月第几周 | 显示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;
更多推荐
所有评论(0)