ORACLE 日期函数相关 格式化处理

-- 查看语言信息
select * from nls_session_parameters;   
select * from V$NLS_PARAMETERS;
select sysdate from dual;

-- 转换日期 不同的格式转换的结果都为2017/03/23
select to_char(to_date('2017.1月.1','rrrr.mon.dd'),'dd.mon.yy')
       ,to_char(sysdate,'rrrr.mon.dd')
from dual;

select to_date('20170323','yyyymmdd')
       ,to_date('03232017','mmddyyyy')
       ,to_date('2017/23/03','yyyy/dd/mm')
       ,to_date('2017-23-03','yyyy/dd/mm')
from dual;
-- 转换日期
select to_date(1123,'rrrr') as 年,to_date(12,'mm') as 月
       ,to_date(3,'dd') as 日,to_date(2,'hh24') as 小时
       ,to_date(59,'mi') as 分, to_date(84600,'sssss') as 秒
from dual;
-- 转换时间 省略日期会以当前系统日期所在月的第一天作为日期部分
select to_date('122412','hhmiss')
       ,to_date('20170101122412','yyyymmddhhmiss')
       ,to_date('20170101122412','yyyymmddhhmiss')
       ,to_date('154','sssss')
       ,to_date('15','mi')
       ,to_date('26','ss')
from dual;
-- 取得本月最后一天
select last_day(sysdate) from dual;

-- 增加月
select add_months(sysdate,12) from dual;

-- 取得两个日期之间的月份
select months_between(to_date('20170909','yyyymmdd'), to_date('20170101','yyyymmdd'))
       ,months_between(to_date('20170901','yyyymmdd'), to_date('20170909','yyyymmdd'))
from dual;

-- 取得两个日期之间的所有连续月份
select add_months(to_date('20170101','yyyymmdd'),level - 1 ) from dual
connect by
level <= months_between(to_date('20170901','yyyymmdd'),to_date('20170101','yyyymmdd')) + 1 ;

-- 取得两个日期之间的所有连续月份
select add_months(to_date('20170101','yyyymmdd'),level - 1 ) from dual
connect by
level <= months_between(to_date('20170901','yyyymmdd'),to_date('20170101','yyyymmdd')) + 1 ;

-- 取得日期的年部分
select  to_char(sysdate,'yy'),to_char(sysdate,'yyy'),to_char(sysdate,'yyyy')
from dual;

-- 取得日期的月部分
select  to_char(sysdate,'mm'),to_char(sysdate,'mon'),to_char(sysdate,'month')
from dual;

-- 取得日期的日部分 可以无限匹配获取多个返回值
select  to_char(sysdate,'dd'),to_char(sysdate,'dddd'),to_char(sysdate,'dddddddd')
        ,to_char(sysdate,'dy'),to_char(sysdate,'day'),to_char(sysdate,'ddspth')
from dual;

-- 取得日期的日部分 语言选择
select   to_char(sysdate,'day','NLS_DATE_LANGUAGE = American')
        ,to_char(sysdate,'dy','NLS_DATE_LANGUAGE = American')
        ,to_char(sysdate,'month','NLS_DATE_LANGUAGE = American')
        ,to_char(sysdate,'mon','NLS_DATE_LANGUAGE = American')
from dual;

-- 取得时间的小时部分 可以无限匹配获取多个返回值
select  to_char(sysdate,'hh'),to_char(sysdate,'hh24hh24') from dual;

-- 取得时间的分钟部分 可以无限匹配获取多个返回值
select  to_char(sysdate,'mi') from dual;

-- 取得时间的秒数部分 可以无限匹配获取多个返回值 一天统计的秒数
select  to_char(sysdate,'ss'),to_char(sysdate,'sssss') from dual;

-- 时间相关格式化
select to_char(sysdate,'am hh12:mi:ss') from dual;
select to_char(sysdate,'amhh24miss') from dual;
-- 取得季度
select to_char(sysdate,'q') from dual;

-- 当月第几周，当年第几周
select to_char(sysdate,'w'),to_char(sysdate,'ww') from dual;

-- 数值转换成英文
select to_char( to_date(88888,'J'),'Jsp') from dual;
 
-- 日期填充空值
select 1, TO_DATE(null) from dual;

-- rrrr与yyyy区别
-- 0-49 以2000 加 50-99 以1900 加
select to_date(23,'yyyy'),to_date(23,'rrrr')
       ,to_date(50,'yyyy'),to_date(50,'rrrr')
       ,to_date(123,'yyyy'),to_date(123,'rrrr')
       ,to_date('23','yyyy'),to_date('23','rrrr')
from dual;

-- 日期截断  返回值还是完整日期 被截断部分显示为0
select trunc(sysdate,'month') 月,trunc(to_date(23,'yyyy'),'rrrr') 年
       ,trunc(to_date(23,'rrrr'),'yyyy') 年,to_date(to_char(sysdate,'rrrr'),'yyyy') as Yea
from dual;
-- 日截断 返回当前日所在星期的第一天日期
-- 截断只与要截部分有关不存在四舍五入问题
-- 不能截断到秒
select trunc(sysdate) as 默认截断,trunc(sysdate,'day')
       ,trunc(to_date('20170318121111','yyyymmddhhmiss'),'day')
       ,trunc(to_date('20170319','yyyymmdd'),'day')
       ,trunc(to_date('2017031910','yyyymmddhhmiss'),'hh')
       ,trunc(to_date('20170319121212','yyyymmddhhmiss'),'mi')
from dual;

-- 日期四舍五入处理 默认是最接近日
select round(sysdate),round(sysdate,'dd')
       ,round(sysdate,'yyyy')
       ,round(sysdate,'mm')
       ,round(sysdate,'hh')
       ,round(sysdate,'mi')      
from dual;

-- 日期四舍五入处理 测试验证 中间值是多少
-- 年按月份舍入 比较值  7
-- 月份按日舍入 比较值 15 大于加1 小于当月
-- 日按小时舍入 比较值 12 大于等于加1
-- 小时按分舍入 比较值 30 大于等于加1
-- 分按秒舍入 比较值 30 大于等于加1
select round(to_date('1999','yyyy')),round(to_date('1999','yyyy'),'dd')
       ,round(to_date('199907','yyyymm'),'yyyy') 年舍
       ,round(to_date('20000216','yyyymmddhh24'),'mm') 月舍
       ,round(to_date('2000020212','yyyymmddhh24'),'dd') 日入
       ,round(to_date('200002021330','yyyymmddhh24mi'),'hh') 时入 
       ,round(to_date('20000202133130','yyyymmddhh24miss'),'mi') 分入      
from dual;

-- 日期计算问题
select to_char(sysdate,'ss')  from dual;

-- 下周第一天 以周日开始计算 1-7
select next_day(sysdate,1) 下周第一天 from dual;
 
-- 时区转换
select new_time(sysdate,'est','gmt'),new_time(sysdate,'gmt','est'),sysdate from dual;

-- 返回最大值 可以用于返回最晚日期
select greatest(to_date('2017/01/01','yyyy/mm/dd'),to_date('2017/01/2月','yyyy/dd/mon'),to_date('2017/1月/03','yyyy/mon/dd')) from dual;

-- 获得某年天数
select
   add_months(trunc(to_date(2000,'yyyy'),'year'),12) - trunc(to_date(2000,'yyyy'),'year')
from dual;
-- 闰年判断方法
select
   decode(to_char(last_day(add_months(trunc(to_date(2004,'yyyy'),'yyyy'),1)),'dd'),28,'平年','闰年')
from dual;

-- 取得第一天和最后一天
select trunc(to_date(20040328,'yyyymmdd'),'mm') as FirstDay,to_char(add_months(last_day(sysdate) +1, -1), 'yyyymmdd'),last_day(sysdate) from dual;

-- 日期或间隔值的字段值 析出方式 准确计算两个日期之间的间隔
-- 数字表示毫秒精度值 转换时间戳和设置毫秒方式
select systimestamp(3) from dual;
select to_timestamp('2017-03-24 11:11:00.123456','yyyy-mm-dd hh24:mi:ssxff')
from dual;
-- 关键参数year,month,day,hour,minute,second
select extract(hour from timestamp '2017-03-24 15:16:30') from dual;
select extract(day from date '2017-03-24') from dual;
-- 日期信息
select extract(year from systimestamp)
       ,extract(month from systimestamp)
       ,extract(day from systimestamp)
from dual;
-- 时间信息
select extract(hour from systimestamp)
       ,extract(minute from systimestamp)
       ,extract(second from systimestamp)
from dual;
-- 时区信息
select extract(timezone_hour from systimestamp) as 时区
       ,extract(timezone_minute from systimestamp) as 分区
       ,extract(timezone_region from systimestamp) as 区域
       ,extract(timezone_abbr from systimestamp) as 国家
from dual;
-- 获得两个日期之间的时间间隔 只支持日一下的精度 月不支持
select extract(day from dt2-dt1) as DD
       ,extract(hour from dt2-dt1) as HH
       ,extract(minute from dt2-dt1) as MI
       ,extract(second from dt2-dt1) as SE
from
(
       select to_timestamp('20170101121212.123456','yyyymmddhhmissxff') as dt1
              ,to_timestamp('20170808111111.456','yyyymmddhhmissxff') as dt2
       from dual
)

-- 取得毫秒数
select to_char(systimestamp,'ssff') from dual;