Oracle11g: datetime

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173

--上一月，上一年 select add_months(sysdate,-1) last_month,add_months(sysdate,-12) last_year from dual;   --下一月，下一年 select add_months(sysdate,1) last_month,add_months(sysdate,12) last_year from dual;   --当月最后一天   select LAST_DAY(sysdate) from dual;   --下周日期   select next_day(sysdate,'星期五') "下周五" from dual; select next_day(sysdate, 'Friday') "Next Friday" from dual; select next_day(sysdate, 4) from dual;   --固定日期一天 select * from account a where a.applytime>= to_date('2011-02-26','yyyy-mm-dd') and a.applytime< to_date('2011-02-27','yyyy-mm-dd'); --前天一天 select * from account a where a.applytime>= to_date(to_char(sysdate-2,'yyyy-mm-dd'),'yyyy-mm-dd') and a.applytime< to_date(to_char(sysdate-1,'yyyy-mm-dd'),'yyyy-mm-dd'); -- 昨天一天 select * from dxw_tmp a where a.applytime>= to_date(to_char(sysdate-1,'yyyy-mm-dd'),'yyyy-mm-dd') and a.applytime< to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'); --今天一天 select * from account a where a.applytime>= to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and a.applytime< to_date(to_char(sysdate+1,'yyyy-mm-dd'),'yyyy-mm-dd');   -- 上月第一天 select to_date(to_char(add_months(sysdate,-2)+1,'yyyy-mm-dd'),'yyyy-mm-dd') from dual ; -- 上月最后一天 select to_date(to_char(add_months(sysdate,-1),'yyyy-mm-dd'),'yyyy-mm-dd') from dual;   select * from account a where a.applytime>= to_date(to_char(add_months(sysdate,-2)+1,'yyyy-mm-dd'),'yyyy-mm-dd') and a.applytime<= to_date(to_char(add_months(sysdate,-1),'yyyy-mm-dd'),'yyyy-mm-dd');   select to_date(to_char(add_months(sysdate,-2),'yyyy-mm-dd'),'yyyy-mm-dd') from dual; select to_date(to_char(add_months(sysdate,-3)+1,'yyyy-mm-dd'),'yyyy-mm-dd') from dual ;      --trunc 截取日期   --当天零点   select trunc(sysdate,'dd') from dual;   --当月一号   select trunc(sysdate,'mm') from dual;   --本年一月一号   select trunc(sysdate,'yyyy') from dual;   --当前毫秒级时间 6位   select to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss.ff6') from dual;     --字符串转为 timestamp类型   select to_timestamp('2012-02-03 10:29:46.453234','yyyy-mm-dd hh24:mi:ss.ff6') from dual;   --timestamp转为字符型   select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff6') from dual;   select cast(sysdate as timestamp) from dual;   select to_char(sysdate,'YYYYMMDD W HH24:MI:SS') from dual; select to_char(sysdate,'YYYYMMDD WW HH24:MI:SS') from dual;   select to_char(sysdate,'YYYYMMDD WW HH24:MI:SS') from dual;   select to_char(systimestamp,'YYYYMMDD WW HH24:MI:SS.FF6') from dual;     -- 月第几周 select to_char(sysdate,'yyyy-mm-dd w hh24:mi:ss') from dual; -- 年第几周 select to_char(sysdate,'yyyy-mm-dd ww hh24:mi:ss') from dual;   -- 第几季度  FF6 date 没有6位的秒位 select to_char(sysdate,'yyyy-mm-dd q ww hh24:mi:ss') from dual;   select to_char(systimestamp,'yyyy-mm-dd q ww hh24:mi:ss.ff6') from dual;   select to_char(sysdate,'SSSSS') from dual;     /* O_DATE格式(以时间:2007-11-02   13:45:25为例)              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 floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual        --时间差-年   select ceil(months_between(sysdate,to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual        --时间差-月 select months_between('19-12月-1999','19-3月-1999') mon_between from dual; select months_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual;   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;   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 ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60 * 1000) 相差豪秒数 FROM DUAL; select ceil((To_date('2018-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2018-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60) 相差秒数 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;  --加7天 select   sysdate+7   from   dual;      -- https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG256   declare ddate timestamp; sdate date; begin   ddate:= systimestamp;   sdate:=sysdate; DBMS_OUTPUT.PUT_LINE(ddate); DBMS_OUTPUT.PUT_LINE(sdate); end;