Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1169666
  • 博文数量: 178
  • 博客积分: 2776
  • 博客等级: 少校
  • 技术积分: 2809
  • 用 户 组: 普通用户
  • 注册时间: 2012-03-22 15:36
文章分类

全部博文(178)

文章存档

2014年(3)

2013年(66)

2012年(109)

分类: Oracle

2013-03-08 11:01:36

Oracle日期格式&操作

日期格式:

        Year:     

        yy two digits 两位年                显示值:07

        yyy three digits 三位年                显示值:007

        yyyy four digits 四位年                显示值:2007

           

        Month:     

        mm    number     两位月              显示值:11

        mon    abbreviated 字符集表示          显示值:11,若是英文版,显示nov    

        month spelled out 字符集表示          显示值:11,若是英文版,显示november

         

        Day:     

        dd    number         当月第几天        显示值:02

        ddd    number         当年第几天        显示值:02

        dy    abbreviated 当周第几天简写    显示值:星期五,若是英文版,显示fri

        day    spelled out   当周第几天全写    显示值:星期五,若是英文版,显示friday       

        ddspth spelled out, ordinal twelfth

            

              Hour:

              hh    two digits 12小时进制            显示值:01

              hh24 two digits 24小时进制            显示值:13

             

              Minute:

              mi    two digits 60进制                显示值:45

             

              Second:

              ss    two digits 60进制                显示值:25

             

              其它

              Q     digit         季度                  显示值:4

              WW    digit         当年第几周            显示值:44

              W    digit          当月第几周            显示值:1

             

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

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

    

select sysdate from dual;     //2013/1/6 16:12:54

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;      //2013-01-06 16:13:48

select to_char(sysdate,'yyyy') as nowYear   from dual;                     //2013

select to_char(sysdate,'mm')    as nowMonth from dual;                     //01 

select to_char(sysdate,'dd')    as nowDay    from dual;                    //06

select to_char(sysdate,'hh24') as nowHour   from dual;                     //16

select to_char(sysdate,'mi')    as nowMinute from dual;                    //13

select to_char(sysdate,'ss')    as nowSecond from dual;                    //48

select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual    //2004/5/7 13:23:44

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;        //星期一

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;    //monday    

select floor(sysdate - to_date('20121231','yyyymmdd')) from dual;          //6     // 两个日期间的天数 

select to_date(null) from dual;            //

between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')  //那么1231号中午12点之后和121号的12点之前是不包含在这个范围之内的。     

//所以,当时间需要精确的时候,觉得to_char还是必要的

select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;   //1

select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;   //1.03225806451613

select next_day(sysdate,'星期一') from dual                      //2013/1/7 16:32:16

select next_day(sysdate,2) from dual                             //2013/1/7 16:38:53

select next_day(sysdate,'星期日') from dual                      //2013/1/13 16:33:05

select next_day(to_date('2013-01-04','yyyy-mm-dd'),'星期二') from dual;   //2013/1/8

日期操作:

时间差

select (sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365 from dual;       //5.18365449010654

select to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365 from dual; //5.18365537798072

select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual      //5    //时间差-

select ceil(months_between(sysdate,to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual     //63     //时间差-

select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual             //时间差-

select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual         //时间差-

select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual    //时间差-

select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //时间差-

修改时间

select sysdate from dual;    //2013/1/6 17:08:53

加法

select add_months(sysdate,12) from dual;   //2014/1/6 17:08:53                                   --1

select add_months(sysdate,1) from dual;    //2013/2/6 17:08:53                                   --1

select to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual; //2013-01-13 17:08:53               --1星期

select to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual;  //2013-01-07 17:08:53              --1

select to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual;  //2013-01-06 18:08:53           --1小时

select to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual;   //2013-01-06 17:09:53       --1分钟

select to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual;     //2013-01-06 17:08:54  --1

减法

select add_months(sysdate,-12) from dual;                    //2012/1/6 17:08:53                 --1

select add_months(sysdate,-1) from dual;                     //2012/12/6 17:08:53                --1

select to_char(sysdate-7,'yyyy-mm-dd HH24:MI:SS') from dual; //2012-12-30 17:08:53               --1星期

select to_char(sysdate-1,'yyyy-mm-dd HH24:MI:SS') from dual; //2013/1/5 17:08:53                 --1

select to_char(sysdate-1/24,'yyyy-mm-dd HH24:MI:SS') from dual;     //2013/1/6 16:08:53          --1小时

select to_char(sysdate-1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual;  //2013/1/6 17:07:53          --1分钟

select to_char(sysdate-1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual;  //2013/1/6 17:08:52       --1                 类推至毫秒0.001秒 oracle认证http://www.cuug.com/


阅读(1618) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~