Oracle中时间和日期函数总结

查看当前日期格式：select * from nls_session_parameters where parameter='NLS_DATE_FORMAT';

修改日期的格式:   alter session set nls_date_format = 'yyyy-mm-dd';

永久设置日期格式：改注册表oracle/HOME0 加字符串NLS_DATE_FORMAT 值yyyy-mm-dd；

1.dd-mon-yy转换为yyyy-mm-dd

  select to_char(to_date('01-5月-05','dd-mon-yy'),'yyyy-mm-dd') from dual

2.计算2009-05-01与2008-04-30的月份差

select (extract(year from to_date('2009-05-01 ','yyyy-mm-dd')) - extract(year from to_date('2008-04-30','yyyy-mm-dd'))) * 12 +

        extract(month from to_date('2008-05-01','yyyy-mm-dd')) - extract(month from to_date('2008-04-30','yyyy-mm-dd')) months from dual;

--extract:用于从日期时间值中取得所需要的特定数据 year.month.day.hour.minute.second

select ceil((to_date('2009-05-01','yyyy-mm-dd') - to_date('2008-04-30','yyyy-mm-dd'))/30) from dual;

select ceil(months_between(to_date('2009-05-01','yyyy-mm-dd'),to_date('2008-04-30','yyyy-mm-dd'))) from dual; 

--查看现在距2008-08-08已过去了多少个月

select ceil(months_between(sysdate,to_date('2008-08-08','yyyy-mm-dd'))) from dual; 

3.年月日时分秒计算

select to_date('2010-04-27 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual; --字符串转换成日期

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;  --日期转化为字符串   

select to_char(sysdate,'yyyy') as nowYear   from dual;    --获取时间的年    'yyyy.yyy.yy.y'分别显示不同年的计数位

--获取时间的月

select to_char(sysdate,'mm')    from dual;     --

select to_char(sysdate,'mon')   from dual;     --中文版：4月  英文版: apr

select to_char(sysdate,'month') from dual;     --中文版：4月  英文版: april

--获取时间的日

select to_char(sysdate,'dd')    from dual;      --当月的第几天

select to_char(sysdate,'ddd')   from dual;      --当年的第几天

select to_char(sysdate,'d')     from dual;      --当周的第几天  select to_char(sysdate,'D') from dual;

select to_char(sysdate,'ddspth')from dual;      --英文显示当月的第几天

--获取时间的时

select to_char(sysdate,'hh24')  from dual;      --24小时制

select to_char(sysdate,'hh')    from dual;      --12小时制

--获取时间的分

select to_char(sysdate,'mi')    from dual;

--获取时间的秒

select to_char(sysdate,'ss')    from dual;  

4.查看星期几

select to_char(sysdate,'dy')    from dual;      --星期几        中文版：星期二  英文版：Tue

select to_char(sysdate,'day')   from dual;      --星期几        中文版：星期二  英文版：Tuesday

--英文显示星期几: to_date('2010-04-27','yyyy-mm-dd') 部分可换成sysdate或其他时间

select to_char(to_date('2010-04-27','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;  --小写

select to_char(to_date('2010-04-27','yyyy-mm-dd'),'DAY','NLS_DATE_LANGUAGE = American') from dual;  --大写

select to_char(to_date('2010-04-27','yyyy-mm-dd'),'Day','NLS_DATE_LANGUAGE = American') from dual;  --首字母大写 

--设置会话日期语言格式 : alter session set nls_date_language='american';   

5. next_day函数:计算机当前日期的下一个星期几

next_day(sysdate,6)是从当前开始下一个星期五(n-1)。后面的数字是从星期日开始算起。　

1　2　3　4　5　6　7　

日 一 二 三 四 五 六　

--本月的第一个星期一

select next_day(to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd'),'星期一') from dual;

select next_day(to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd'),2) from dual

--计算上一个月的第一个星期一

select next_day(add_months(to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd'),-1),'星期一') from dual;

select next_day(add_months(to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd'),-1),2) from dual;

--计算下一个月的第一个星期一

select next_day(add_months(to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd'),1),'星期一') from dual;

select next_day(add_months(to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd'),1),2) from dual;

6.判断一年是不是闰年

--如果是28-平年, 29-闰年

select to_char( last_day( to_date('' ||&year,'mmyyyy') ), 'dd' ) from dual;

select to_char( last_day( to_date('' ||extract(year from sysdate),'mmyyyy') ), 'dd' ) from dual;

select to_char(last_day(add_months(trunc(sysdate,'year'),2)-1),'dd') from dual;

select decode(to_char(last_day(to_date(to_char(sysdate,'yyyy')||'-02-01','yyyy-mm-dd')),'dd'),'','平年','闰年') from dual

7.计算今年的天数:select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual

8.计算本月的第一天:

select to_char(last_day(add_months(sysdate,-1))+1,'yyyy-mm-dd') from dual;

select to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd')  from dual;

9.计算本月最后一天

--select last_day(sysdate)from dual;

select last_day(trunc(sysdate)) from dual;

select trunc(last_day(add_months(sysdate,0))) from dual;

select add_months(to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd'),1)-1 from dual;

select last_day(to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd'))  from dual;

select add_months(to_date(to_char(last_day(add_months(sysdate,-1))+1,'yyyy-mm-dd'),'yyyy-mm-dd'),1)-1 from dual;

10.上个月最后一天:

select trunc(last_day(add_months(sysdate,-1))) from dual;

select to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd')-1  from dual;

--select trunc(last_day(add_months(sysdate,-1)))+1-1/24/60/60 from dual

11.last_day(trunc(sysdate))扩展

--本月第一天

select add_months(last_day(trunc(sysdate)),-1)+1 from dual;

--上个月第一天

select add_months(last_day(trunc(sysdate)),-2)+1 from dual;

--下个月第一天

select add_months(last_day(trunc(sysdate)),0)+1 from dual;

--本月最后一天

select last_day(trunc(sysdate))from dual;

--上个月最后一天

select add_months(last_day(trunc(sysdate)),-1) from dual;

--下个月最后一天

select add_months(last_day(trunc(sysdate)),1) from dual;

--计算本月的天数

select last_day(trunc(sysdate)) - (add_months(last_day(trunc(sysdate)),-1)+1)+1 from dual;

--select trunc(last_day(sysdate))- to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd')+1 from dual

--计算上个月的天数

select add_months(last_day(trunc(sysdate)),-1) - (add_months(last_day(trunc(sysdate)),-2)+1)+1 from dual;

--计算下个月的天数

select add_months(last_day(trunc(sysdate)),1) - (add_months(last_day(trunc(sysdate)),0)+1)+1 from dual;

12.trunc(sysdate,'month') 扩展;

--本月的第一天

select trunc(sysdate,'month') from dual;

--本月最后一天

select last_day(trunc(sysdate, 'month')) from dual;

--上个月的第一天

select trunc(trunc(sysdate, 'month') - 1, 'month') from dual;

--上个月的最后一天

select trunc(sysdate, 'month') - 1 from dual;

--下个月的第一天

select last_day(trunc(sysdate, 'month'))+1 from dual;

--下个月的最后一天

select last_day(last_day(trunc(sysdate, 'month'))+1)from dual;

13.计算本年的第一天

select trunc(sysdate,'year') from dual;

select trunc(sysdate,'yyyy') from dual;

select to_date(to_char(sysdate,'yyyy')||'-01-01','yyyy-mm-dd') from dual;

14.计算本年的最后一天

select add_months(trunc(sysdate,'year'),12)-1 from dual;

select to_date(to_char(sysdate,'yyyy')||'-12-31','yyyy-mm-dd') from dual;

select add_months(to_date(to_char(sysdate,'yyyy')||'-01-01','yyyy-mm-dd'),12)-1 from dual;

select add_months(last_day(to_date(to_char(sysdate,'yyyy')||'-01-01','yyyy-mm-dd')),11) from dual;

15.计算两个日期间的星期几的总数

--计算2010-04-28至2010-10-01之间的工作日的总数

select count(*)

  from (select rownum-1 rnum

          from all_objects

         where rownum <= to_date('2010-10-01','yyyy-mm-dd') - to_date('2010-04-28','yyyy-mm-dd')+1

        )

 where to_char( to_date('2010-04-28','yyyy-mm-dd')+rnum, 'day')not in ( '星期六', '星期日' );  

--替换说明: all_objects --> dual;  where rownum --> connect by rownum/level; day --> d/D, dy

--"星期六"、"星期日" --> 1,7 或单个数字 'sat'.'sun'; not in --> in; count(*) --> * ; 日期可替换成替他形式.

16.查看当前系统时间的四种方法:

select sysdate from dual;

select current_date from dual;

select add_months(sysdate, 0)  from dual;

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

17.计算本周星期几的日期

--1-7:oracle默认一周从星期日开始

select trunc(sysdate,'day')+1 from dual;

select trunc(sysdate,'day')+2 from dual;

select trunc(sysdate,'day')+3 from dual;

select trunc(sysdate,'day')+4 from dual;

select trunc(sysdate,'day')+5 from dual;

select trunc(sysdate,'day')+6 from dual;

select trunc(sysdate,'day')+7 from dual;

--0-6:iso默认一周从星期一开始

select trunc(sysdate,'iw')+0 from dual;

select trunc(sysdate,'iw')+1 from dual;

select trunc(sysdate,'iw')+2 from dual;

select trunc(sysdate,'iw')+3 from dual;

select trunc(sysdate,'iw')+4 from dual;

select trunc(sysdate,'iw')+5 from dual;

select trunc(sysdate,'iw')+6 from dual;

18.如何求一年的总天数

把下一年的1.1减当年的1.1

select add_months(trunc(sysdate,'yyyy'),12)-trunc(sysdate,'yyyy') from dual 

select case when (mod(to_number(to_char(sysdate,'YYYY')),4)  =0 or mod(to_number(to_char(sysdate,'YYYY')),100 ) =0)

              and mod(to_number(to_char(sysdate,'YYYY')),400 ) <>0

            then 366 else 365

        end total_days

from dual

19.计算奥运会距离现在的时间:

select '奥运会距今已过:' || trunc(dt) || '天' ||

      trunc((dt - trunc(dt)) * 24) || '小时' ||

      trunc((dt * 24 - trunc(dt * 24)) * 60) || '分' ||

      trunc((dt * 1440 - trunc(dt * 1440)) *60) || '秒'

 from(

      select sysdate - to_date('2008-08-08 20:00:00', 'yyyy-mm-dd hh24:mi:ss') dt

        from dual

       );

select extract(day from dt) "天",

       extract(hour from dt) "小时",

       extract(minute from dt) "分",

       trunc(extract(second from dt)) "秒"

  from (

        select systimestamp - to_date('2008-08-08 20:00:00', 'yyyy-mm-dd hh24:mi:ss') dt

          from dual

       );

附：

1. 时间格式范围

hh24:24小时格式下时间范围为： 0:00:00 - 23:59:59

hh12:12小时格式下时间范围为： 1:00:00 - 12:59:59 

2.时间为null的情况

select to_date(null) from dual;

select to_char(null) from dual;

3.时间格式的设置

--系统设置

alter system  set NLS_DATE_LANGUAGE = American

--会话设置

alter session set NLS_DATE_LANGUAGE = American 

3.计算一个季度的总天数

select last_day(to_date(to_char(sysdate,'yyyy-')||lpad(floor(to_number(to_char(sysdate,'mm'))/3)*3+3,2,'')||'-01','yyyy-mm-dd'))

       -to_date(to_char(sysdate,'yyyy-')||lpad(floor(to_number(to_char(sysdate,'mm'))/3)*3+1,2,'')||'-01','yyyy-mm-dd')

       +1

  from dual;

4.判断当前时间是上午还是?

select case when to_number(to_char(sysdate,'hh24')) between 6 and 11  then '上午'

            when to_number(to_char(sysdate,'hh24')) between 12 and 18 then '下午'

            else '晚上'

       end

  from dual;

select case when to_number(to_char(sysdate,'hh24')) between 1 and 5  then '凌晨'

            when to_number(to_char(sysdate,'hh24')) between 6 and 11  then '上午'

            when to_number(to_char(sysdate,'hh24')) between 12 and 18 then '下午'

            when to_number(to_char(sysdate,'hh24')) between 19 and 24 then '晚上'

       end

  from dual;

5.查找月份差:months_between(date,date)

select months_between(to_date('01-31-2010','mm-dd-yyyy'),to_date('12-31-2009','mm-dd-yyyy')) "months" from dual;      

6.查看某一天距今天的天数：select trunc(sysdate)-to_date('','yyyymmdd') from dual;

7. 一年以后的今天: select add_months(sysdate,12)  from dual;

   一年以前的今天: select add_months(sysdate,-12) from dual;

   系统当前时间:   select add_months(sysdate, 0)  from dual;

8.从今天零点之后经过的秒数：select to_char(sysdate,'SSSSS') from dual;

9.本周星期几(n)的日期: select trunc(sysdate,'day') + n from dual;

10.本月的天数:select to_char(last_day(sysdate),'dd') days from dual

11.计算当前月所有星期五的日期

select to_char(dt,'yyyy-mm-dd')

  from(select trunc(sysdate,'mm')+rownum-1 dt

         from dual connect by rownum<=31

       )t

 where to_char(t.dt,'mm') = to_char(sysdate,'mm')

   and to_char(t.dt,'day')= '星期五';

--计算当前月所有星期五的日期(以","隔开)

select wm_concat(to_char(dt,'yyyy-mm-dd'))

  from(select trunc(sysdate,'mm')+rownum-1 dt from dual connect by rownum<=31)t

 where to_char(t.dt,'mm') = to_char(sysdate,'mm')

   and to_char(t.dt,'day')= '星期五';

--计算当前月之前3个月的所有星期五的日期

select to_char(dt,'yyyy-mm-dd')

  from(select trunc(sysdate,'mm')+rownum-1 dt from dual connect by rownum<= (select add_months(sysdate,3)-trunc(sysdate)))t

 where to_char(t.dt,'day')= '星期五';

Oracle中时间和日期函数总结的更多相关文章

mysql 中时间和日期函数应用
一.MySQL 获得当前日期时间函数 1.1 获得当前日期+时间(date + time)函数:now() mysql> select now(); +-------------------- ...
mysql 中时间和日期函数
From: http://www.cnblogs.com/redfox241/archive/2009/07/23/1529092.html 一.MySQL 获得当前日期时间函数 1.1 获得当前日 ...
oracle中110个常用函数介绍
1. ASCII 返回与指定的字符对应的十进制数; SQL> select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dua ...
Oracle中的内置函数在sql中的转换整理
程序里面经常会即支持Oracle数据库,又支持sql数据库.而有些Oracle内置函数用的比较多,但在sql中语法有些不同,我做了些整理,希望可以帮助大家.... 1.oracle中的内置函数:ora ...
Python 关于时间和日期函数使用 -- (转)
python中关于时间和日期函数有time和datatime 1.获取当前时间的两种方法: import datetime,time now = time.strftime("%Y-%m ...
标准c时间与日期函数
标准c时间与日期函数 asctime 语法: #include <time.h> char *asctime( const struct tm *ptr ); 功能: 函数将p ...
用sql语句导出oracle中的存储过程和函数
用sql语句导出oracle中的存储过程和函数: SET echo off ; SET heading off ; SET feedback off ; SPOOL 'C:/PRC.SQL' repl ...
lua的时间和日期函数
lua的时间和日期函数 -- ::| 分类: Lua | 标签:lua 时间函数 |举报|字号订阅下载LOFTER客户端 --获取当前的时间戳,单位是秒. time=os.time(); pri ...
oracle中时间格式时候的大于号是大于和等于的意思
oracle中时间格式时候的大于号是大于和等于的意思

随机推荐

xen下离线读取虚拟机磁盘镜像的补丁
之前在xen-3.4.2和xen-4.1.2下做过几个基于qemu模拟器的补丁,就是想着不用通过xm create(xen3下面)或xl create(xen4下面)启动虚拟机,而能直接去解析磁盘镜像 ...
聊聊Function的bind()
bind顾名思义,绑定. bind()方法会创建一个新函数,当这个新函数被调用时,它的this值是传递给bind()的第一个参数,它的参数是bind()的其他参数和其原本的参数. 上面这个定义最后一句 ...
Hibernate核心思想—ORM机制（一）
转:http://blog.csdn.net/wanghuan203/article/details/7566518 hibernate是一个采用ORM(Object/Relation Mapping ...
转：c++类实例在内存中的分配
转自:http://blog.csdn.net/alexwei2009/article/details/6157926 c++是一种面向对象的编程语言,它向下保持了对c的兼容,同时也允许程序员能够* ...
ckeditor详解
源网页编辑软件FCKEditor在09年发布更新到3.0,并改名为CKEditor.改进后的ckeditor更加模块话,配置更加灵活,和以前的fckeditor使用方式上也有所不同.在我的mvc项目中 ...
电子科大POJ &quot&semi;整数的千位分隔&quot&semi;
整数的千位分隔 Time Limit: 3000/1000MS (Java/Others) Memory Limit: 65535/65535KB (Java/Others) C-source ...
log4net简单用法
一.NuGet在Server,mvc中添加Common.Logging和common.Logging.Log4Net如下图二.在Server层创建logger类 <?xml version=& ...
Springboot security cas源码陶冶-CasAuthenticationFilter
Springboot security cas整合方案中不可或缺的校验Filter类或者称为认证Filter类,其内部包含校验器.权限获取等,特开辟新地啃啃继承结构 - AbstractAuthen ...
PHP（数据类型、水仙花数（重点）运算符）
<!--三个弹窗 alert():打开页面只是提示一下,告警框仅仅提示,关了就关了 confirm():选择框有返回值 prompt():输入框控制台输出console.log() 字符串 ...
ThinkPHP 5&period;2 出 RC1 版本 RC 是什么意思呢？
ThinkPHP 5.2 出 RC1 版本 RC 是什么意思呢? RC 的意思是软件候选版本,就是不会有很大的改变,主要还是在除错方面. 来自收集的资料1引用: Alpha:是内部测试版,一般不向外部 ...