Oracle 操作时间的SQL语句
---数据库版本
select * from v$version
---SCOTT默认表空间以及用户
select u.username,u.default_tablespace from dba_users u where u.username='SCOTT'
select * from database_properties dp where dp.property_name='DEFAULT_PERMANENT_TABLESPACE';
select u.username,u.default_tablespace from dba_users u where u.username='dborcl'
select * from dba_tablespaces
---1.首先，创建（新）用户：
create user gaohao identified by gaohao;
--也可以不创建新用户，而仍然用以前的用户，如：继续利用scott用户
---2.创建表空间：
create tablespace ZTTM datafile 'd:\oracle\data.dbf' size 500M;
---3.将空间分配给用户：
alter user gaohao default tablespace ZTTM;
---将名字为tablespacename的表空间分配给username
---4.给用户授权：
grant create session,create table,unlimited tablespace to gaohao;
---5.然后再以楼主自己创建的用户登录，登录之后创建表即可。
create user ZTTM identified by zttm;
alter user ZTTM default tablespace ZTTM;
grant create session,create table,unlimited tablespace to ZTTM;
---建表
create table UserInfo
(
USERID int primary key ,
UNAME varchar(20) not null,
ISMARRAY char(5) not null,
ISONJOB char(5) not null,
REMARK varchar(500) null
)
insert into UserInfo values(1,'Tom','0','0','*****************************');
insert into UserInfo values(2,'JACK','1','0','JJJJJJJJJJJJJJJJJJJJJJJJJ');
insert into UserInfo values(3,'JIM','0','1','WWWWWWWWWWWWWWW');
insert into UserInfo values(4,'BOT','1','1','AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
insert into UserInfo values(5,'KATE','1','0','GGGGGGGGGGGGGGGGGGGGGGGG');
insert into UserInfo values(6,'BRAN','0','1','HHHHHHHHHHHHHHHHHHHHHHHHHHHHH');
insert into UserInfo values(7,'JUN','1','1','RRRRRRRRRRRRRRRRRRRRRRRRRRR');
insert into UserInfo values(8,'ANDRUE','1','1','EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE');
insert into UserInfo values(9,'ALICE','1','0','VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV');
insert into UserInfo values(10,'HANKS','0','1','QQQQQQQQQQQQQQQQQQQQQQQQQQQQQ');
insert into UserInfo values(11,'KEBE','0','1','BNNNNNNNNNNNNNNNNNNNNNNNN');
insert into UserInfo values(12,'SAM','1','0','ZXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
insert into UserInfo values(13,'BOB','0','0','TTTTTTTTTTTTTTTTTTTTTTTTTTTT');
select * from UserInfo;
select to_date('20120725115536','yyyy-MM-dd HH24:mi:ss') TimeStamp from dual
grant select on v_$statname to gaohao;
grant select on v_$session to gaohao;
grant select on v_$sesstat to gaohao;
grant select on v_$mystat to gaohao;
grant select on v_$statname to ZTTM;
grant select on v_$session to ZTTM;
grant select on v_$sesstat to ZTTM;
grant select on v_$mystat to ZTTM;
select u.USERID,--员工编号
u.UNAME, --员工姓名
u.ISMARRAY, --婚否
u.ISONJOB,--是否在职
u.REMARK --备注
from UserInfo u
where u.USERID like '%1%'
select to_char(to_date('20120727094755','yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') CurrentTime from dual
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') CurrentTime from dual;
select TO_DATE(null) from dual;
/*SQL语句*/
select * from log_cpuidle where idle_date = (select to_char(sysdate-1,'DD-MON-YY') from dual);
/*其中idle_date是DATE类型的，不知道为啥要将sysdate进行转换，只有这样才能查找到日期是前一天的数据；
其中要注意的是sysdate对应的格式为DD-MON-YY；*/
----------------------------------------------------------------------------
insert into table select a, b, count(*)c,avg(d),e from LOG_TABLE where a=(select to_char(sysdate-1, 'DD-MON-YY') from dual) group by a, b, e order by b;
/*
复合型的insert 语句；
取前几行的数据用rownum；select * from table where rownum < 10;
group by:分组查询，一般和聚合函数（AVG、SUM、MAX、COUNT等）一起使用，它后面可以跟having限制性语句；
order by：select “栏位名” from “表格”[where “条件”] order by “栏位名”[asc, desc]*/
----------------------------------------------------------------------
•/*============创建Customer表==========*/
•create table Customer
•(
• Customer_id number(6) not null,
• Customer_name varchar2(50) not null,
• Password varchar2(20) not null,
• True_name varchar2(20),
• Email_address varchar2(50) not null, --唯一
• Password_question varchar2(50) not null,
• Password_anwser varchar2(50) not null,
• Status char(1), --默认是1，取值0或1
• Customer_level char(1), --默认是1，取值1，2，3
• Score number(6),
• Register_date date, --默认为系统时间
• Login_time timestamp,
• Login_count number(6),
• Login_ip char(6)
•);
•/*===========创建Orders表==========*/
•create table Orders
•(
• Order_id varchar2(10) not null,
• Order_Customer_id number(6) not null,
• Order_date date not null,
• Order_price number not null
•);
•/*==========查询Customer表===========*/
•select * from Customer
•
•/*==========向表中添加数据===========*/
•insert into Customer values
•(220077,'wantingqiang','wtq','万廷强','lovezhqj@qq.com','你是哪个?','wtq','1','3',150,sysdate,sysdate,15789,'172.26')
•
•/*==========修改表Customer===========*/
•alter table Customer
•modify Login_ip char(16);
•
•/*=========修改第一条记录中的ip=====*/
•update Customer set Login_ip='172.26.3.145' where Customer_id='220077';--ip地址修改成功
•
•/*=========添加一个列===============*/
•alter table Customer
•add LoginOut_time date; --新列增加成功
•
•/*=========删除一个列LoginOut_time========*/
•alter table Customer
•drop column LoginOut_time; --列删除成功
•
•/*========给列添加注释===========*/
•comment on column
• Customer.Customer_Name is '客户姓名';
•
•/*========给表添加注释==========*/
•comment on table Customer is '客户表，为了保持与客户的联系';
•
•/*========重新命名表============*/
•alter table Customer rename to Customer_Change;
•
•select * from Customer_Change
•
•alter table Customer_Change rename to Customer;
•
•/*==========添加非空约束========*/
•alter table Customer
•modify status not null; --非空约束添加成功
•
•/*==========添加主键约束========*/
•alter table Customer
•add constraint customer_id_pk primary key(Customer_id); --主键添加成功
•
•/*==========添加外键约束========*/
•--向Orders表中添加外键，与Customer表关联
•--在下面的列子末尾加上：
•--on delete 表示允许级联删除
•--on update 表示允许级联更新
•alter table Orders
•add constraint Orders_Customer_fk foreign key(Order_Customer_id) references Customer(Customer_id);
•--外键添加成功
•/*==========删除外键约束=========*/
•alter table Orders
•drop constraint "ORDERS_CUSTOMER_FK"; --外键删除成功，这个要注意大小写哈
•
•/*=========添加唯一约束========*/
•alter table Customer
•add constraint un_email unique(Email_address); --添加唯一约束成功
•
•
•/*=========修改默认约束========*/
•alter table Customer
•modify Status default('1');
•
•/*=========添加检查约束========*/
•alter table Customer
•add constraint ck_status check(Status in ('1','0')); --检查约束添加成功
•
•
•/*==========禁止检查约束=======*/
•alter table Customer
• disable constraint ck_status; --禁止成功
•
•/*==========激活检查约束=======*/
•alter table Customer
• enable constraint ck_status; --激活成功
•
•/*==========删除检查约束========*/
•alter table Customer
•drop constraint ck_status; --删除检查约束成功
•
•/*==========最后是删除表========*/
•drop table Customer; --删除表成功
/*使用SQL*PLUS（1）
SQL> desc table; 显示表结构
SQL> select * from tab; 查看用户下所有的表
SQL> set pause on; 可以使大量结果集在用户按“Enter”(回车)后翻页
SQL> set pagesize 100; 设定SQL语句返回结果集一页的行数100, 默认值是14
SQL> set linesize 100; 设定SQL语句返回结果集一行的宽度100, 默认值是80*/
------------------------------------------------------------------------------------------
/*常用的日期处理函数
oracle内部以数字格式存储日期和时间信息：世纪，年，月，日，小时，分钟，秒 临时表dual表
缺省格式：DD-MON-YY（'14-3月-08'） round(to_date('14-3月-08'),'month')
可使用sysdate函数（没有参数和括号）获取当前系统日期和时间
日期数据直接加或减一个数值（不一定是整数，代表的是多少天），结果仍为日期（约定的单位为天，当然也可以为月或年）
两个日期数据可以相减（不可相加，没有意义 ），结果为二者相差多少天
add_months(日期x，数值y)：计算在日期x基础上增加y个月后的日期 add_months(sysdate,2)
last_day(s) 返回日期x当月最后一天的日期 last_day(sysdate)
months_between(x,y) 返回日期x和y之间相差的月数 months_between(sysdate,sysdate) 有正负号之分（参数前后关系有关）
round(x,y) 四舍五入将日期x截断到y所指定的日期单位（月或年）的第一天 round(sysdate,'month') 3月14号为3月1号 3月16号为4月1号 年的同理round(sysdate,'year')
trunc(x,y) 将日期x截断到y所指定的日期单位（月或年）的第一天，不四舍五入trunc(sysdate,'month')trunc(sysdate,'year')
next_day(x,y) 计算指定日期x后的第一个星期几（由参数y指定）对应的日期 next_day(sysdate,'星期二')*/
-----------------------------------------------------------------------
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; //获取时间的秒
-------------------------------------------------------------------
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
/*设置两个日期之间的天数；*/
---------------------------------------------------------
select count(*)from (
select rownum-1 rnum
from all_objects where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-02-01','yyyy-mm-dd')+1
)
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' ) not in ( '1', '7' )
---------------------------------------------------------------------------------
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
/*显示今天是一年中的第几天；*/
---------------------------------------------------------------------
/*计算时间差
注:oracle时间差是以天数为单位,所以换算成年月,日
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(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths 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 //时间差-秒*/
--------------------------------------------------------------------------
/*TO_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*/
---------------------------------------------------------------------------
/*日期的比较*/
/*在今天之前:*/
select * from up_date where update < to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update <= to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
/*在今天只后:*/
select * from up_date where update > to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update >= to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
/*精确时间:*/
select * from up_date where update = to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
/*在某段时间内:*/
select * from up_date where update between to_date('2007-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update < to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and update > to_date('2007-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update <= to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and update >= to_date('2007-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
----------------------------------------------------------------------------------
/*日期时间间隔操作*/
/*当前时间减去7分钟的时间 */
select sysdate,sysdate - interval '7' MINUTE from dual
/*当前时间减去7小时的时间 */
select sysdate - interval '7' hour from dual
/*当前时间减去7天的时间 */
select sysdate - interval '7' day from dual
/*当前时间减去7月的时间*/
select sysdate,sysdate - interval '7' month from dual
/*当前时间减去7年的时间*/
select sysdate,sysdate - interval '7' year from dual
/*时间间隔乘以一个数字*/
select sysdate,sysdate - 8 *interval '2' hour from dual
-----------------------------------------------------------------
/*返回当前时间 年月日小时分秒毫秒*/
select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;
--返回当前 时间的秒毫秒，可以指定秒后面的精度(最大=9)
select to_char(current_timestamp(9),'MI:SSxFF') from dual;
------------------------------------------------------------------
/*Oracle SQL 语句对时间操作的总结*/
/*在SQL语句中，常常用会对时间（或日期）进行一些处理，下面是比较通用的一些语句：
延迟：
sysdate+(5/24/60/60) 在系统时间基础上延迟5秒
sysdate+5/24/60 在系统时间基础上延迟5分钟
sysdate+5/24 在系统时间基础上延迟5小时
sysdate+5 在系统时间基础上延迟5天
add_months(sysdate,-5) 在系统时间基础上延迟5月
add_months(sysdate,-5*12) 在系统时间基础上延迟5年
*/
/*上月末的日期：*/
select last_day(add_months(sysdate, -1)) from dual;
/*本月的最后一秒：*/
select trunc(add_months(sysdate,1),'MM') - 1/24/60/60 from dual
/*本周星期一的日期：*/
select trunc(sysdate,'day')+1 from dual
/*年初至今的天数：*/
select ceil(sysdate - trunc(sysdate, 'year')) from dual;
/*今天是今年的第几周 :*/
select to_char(sysdate,'fmww') from dual
/*今天是本月的第几周:*/
SELECT TO_CHAR(SYSDATE,'WW') - TO_CHAR(TRUNC(SYSDATE,'MM'),'WW') + 1 AS "weekOfMon" FROM dual
/*本月的天数*/
SELECT to_char(last_day(SYSDATE),'dd') days FROM dual
/*今年的天数*/
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
/*下个星期一的日期*/
SELECT Next_day(trunc(SYSDATE),'monday') FROM dual
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
/*计算工作日方法*/
create table t(s date,e date);
alter session set nls_date_format = 'yyyy-mm-dd';
insert into t values('2003-03-01','2003-03-03');
insert into t values('2003-03-02','2003-03-03');
insert into t values('2003-03-07','2003-03-08');
insert into t values('2003-03-07','2003-03-09');
insert into t values('2003-03-05','2003-03-07');
insert into t values('2003-02-01','2003-03-31');
-- 这里假定日期都是不带时间的，否则在所有日期前加trunc即可。
select s,e,e-s+1 total_days, trunc((e-s+1)/7)*5 + length(replace(substr('01111100111110',to_char(s,'d'),mod(e-s+1,7)),'0','')) work_days from t;
-- drop table t;
========================================================
/*判断当前时间是上午下午还是晚上*/
SELECT CASE
WHEN to_number(to_char(SYSDATE,'hh24')) BETWEEN 6 AND 11 THEN '上午'
WHEN to_number(to_char(SYSDATE,'hh24')) BETWEEN 11 AND 17 THEN '下午'
WHEN to_number(to_char(SYSDATE,'hh24')) BETWEEN 17 AND 21 THEN '晚上'
END
FROM dual;
==========================================================
/*Oracle 中的一些处理日期*/
/*将数字转换为任意时间格式.如秒:需要转换为天/小时*/
SELECT to_char(floor(TRUNC(936000/(60*60))/24))||'天'||to_char(mod(TRUNC(936000/(60*60)),24))||'小时' FROM DUAL
TO_DATE格式
/*Day:
dd number 12
dy abbreviated fri
day spelled out friday
ddspth spelled out, ordinal twelfth
Month:
mm number 03
mon abbreviated mar
month spelled out march
Year:
yy two digits 98
yyyy four digits 1998
24小时格式下时间范围为： 0:00:00 - 23:59:59....
12小时格式下时间范围为： 1:00:00 - 12:59:59 ....
1. 日期和字符转换函数
用法（to_date,to_char） */
2.
select to_char( to_date(222,'J'),'Jsp') from dual
/*显示Two Hundred Twenty-Two */
/*3. 求某天是星期几 */
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;
/*设置日期语言*/
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
/*也可以这样 */
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
/*4. 两个日期间的天数 */
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
/*5. 时间为null的用法 */
select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual;
/*注意要用TO_DATE(null)*/
/*6.
a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')
那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
所以，当时间需要精确的时候，觉得to_char还是必要的
7. 日期格式冲突问题
输入的格式要看你安装的ORACLE字符集的类型, 比如:
US7ASCII, date格式的类型就是: '01-Jan-01' */
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American
/*或者在to_date中写 */
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
/*注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多， 可查看*/
select * from nls_session_parameters
select * from V$NLS_PARAMETERS
8.
select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
02-01','yyyy-mm-dd')+1
)
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )
not
in ( '1', '7' )
/*查找2002-02-28至2002-02-01间除星期一和七的天数
在前后分别调用DBMS_UTILITY.GET_TIME, 然后将结果相减(得到的是1/100秒, 而不是毫秒). */
9.
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
/*10. Next_day的用法
Next_day(date, day) */
/*Monday-Sunday, for format code DAY
Mon-Sun, for format code DY
1-7, for format code D */
11
select to_char(sysdate,'hh:mi:ss') TIME from all_objects
/*注意：第一条记录的TIME 与最后一行是一样的
可以建立一个函数来处理这个问题 */
create or replace function sys_date return date is
begin
return sysdate;
end;
select to_char(sys_date,'hh:mi:ss') from all_objects;
/*12. 获得小时数 */
SQL> select sysdate ,to_char(sysdate,'hh') from dual;
SYSDATE TO_CHAR(SYSDATE,'HH')
-------------------- ---------------------
2003-10-13 19:35:21 07
SQL> select sysdate ,to_char(sysdate,'hh24') from dual;
SYSDATE TO_CHAR(SYSDATE,'HH24')
-------------------- -----------------------
2003-10-13 19:35:21 19
/*获取年月日与此类似 */
/*13. 年月日的处理 */
select older_date,
newer_date,
years,
months,
abs(
trunc(
newer_date-
add_months( older_date,years*12+months )
)
) days
from ( select
trunc(months_between( newer_date, older_date )/12) YEARS,
mod(trunc(months_between( newer_date, older_date )),
12 ) MONTHS,
newer_date,
older_date
from ( select hiredate older_date,
add_months(hiredate,rownum)+rownum newer_date
from emp )
)
/*14. 处理月份天数不定的办法 */
select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual
/*16. 找出今年的天数 */
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
/*闰年的处理方法 */
to_char( last_day( to_date('02' | | :year,'mmyyyy') ), 'dd' )
/*如果是28就不是闰年 */
/*17. yyyy与rrrr的区别 */
/*'YYYY99 TO_C
------- ----
yyyy 99 0099
rrrr 99 1999
yyyy 01 0001
rrrr 01 2001
18.不同时区的处理 */
select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate
from dual;
/*19. 5秒钟一个间隔 */
Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')
from dual
TO_DATE(FL TO_CH
---------- -----
2007-01-01 60368
/*SSSSS表示5位秒数 */
/*20. 一年的第几天 */
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
TO_ SYSDATE
--- ----------
017 2007-01-17
/*21.计算小时,分,秒,毫秒 */
select
Days,
A,
TRUNC(A*24) Hours,
TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,
TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,
TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
from
(
select
trunc(sysdate) Days,
sysdate - trunc(sysdate) A
from dual
)
select * from tabname
order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');
/*//
floor((date2-date1) /365) 作为年
floor((date2-date1, 365) /30) 作为月
mod(mod(date2-date1, 365), 30) 作为日. */
/*22.next_day函数 */
/*next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。
1 2 3 4 5 6 7
日 一 二 三 四 五 六 */
---------------------------------------------------------------
select (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from dual
/*日期 返回的是天 然后 转换为ss */
select (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60 from dual ;
select (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24 from dual ;
select (sysdate-to_date('2012-07-23 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24 from dual ;
select (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from dual ;
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 ....
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; //获取时间的秒
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual//
2.
select to_char( to_date(222,'J'),'Jsp') from dual
显示Two Hundred Twenty-Two
3.求某天是星期几
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
设置日期语言
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
也可以这样
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
4. 两个日期间的天数
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
5. 时间为null的用法
select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual;
注意要用TO_DATE(null)
6.月份差
a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')
那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
所以，当时间需要精确的时候，觉得to_char还是必要的
7. 日期格式冲突问题
输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01'
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American
或者在to_date中写
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，
可查看
select * from nls_session_parameters
select * from V$NLS_PARAMETERS
8.
select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
02-01','yyyy-mm-dd')+1
)
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )
not in ( '1', '7' )
查找2002-02-28至2002-02-01间除星期一和七的天数
在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒).
9. 查找月份
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
10. Next_day的用法
Next_day(date, day)
Monday-Sunday, for format code DAY
Mon-Sun, for format code DY
1-7, for format code D
11
select to_char(sysdate,'hh:mi:ss') TIME from all_objects
注意：第一条记录的TIME 与最后一行是一样的
可以建立一个函数来处理这个问题
create or replace function sys_date return date is
begin
return sysdate;
end;
select to_char(sys_date,'hh:mi:ss') from all_objects;
12.获得小时数
extract()找出日期或间隔值的字段值
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer
SQL> select sysdate ,to_char(sysdate,'hh') from dual;
SYSDATE TO_CHAR(SYSDATE,'HH')
-------------------- ---------------------
2003-10-13 19:35:21 07
SQL> select sysdate ,to_char(sysdate,'hh24') from dual;
SYSDATE TO_CHAR(SYSDATE,'HH24')
-------------------- -----------------------
2003-10-13 19:35:21 19
13.年月日的处理
select older_date,
newer_date,
years,
months,
abs(
trunc(
newer_date-
add_months( older_date,years*12+months )
)
) days
from ( select
trunc(months_between( newer_date, older_date )/12) YEARS,
mod(trunc(months_between( newer_date, older_date )),12 ) MONTHS,
newer_date,
older_date
from (
select hiredate older_date, add_months(hiredate,rownum)+rownum newer_date
from emp
)
)
14.处理月份天数不定的办法
select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual
16.找出今年的天数
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
闰年的处理方法
to_char( last_day( to_date('02' | | :year,'mmyyyy') ), 'dd' )
如果是28就不是闰年
17.yyyy与rrrr的区别
'YYYY99 TO_C
------- ----
yyyy 99 0099
rrrr 99 1999
yyyy 01 0001
rrrr 01 2001
18.不同时区的处理
select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate
from dual;
19.5秒钟一个间隔
Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')
from dual
2002-11-1 9:55:00 35786
SSSSS表示5位秒数
20.一年的第几天
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
310 2002-11-6 10:03:51
21.计算小时,分,秒,毫秒
select
Days,
A,
TRUNC(A*24) Hours,
TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,
TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,
TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
from
(
select
trunc(sysdate) Days,
sysdate - trunc(sysdate) A
from dual
)
select * from tabname
order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');
//
floor((date2-date1) /365) 作为年
floor((date2-date1, 365) /30) 作为月
d(mod(date2-date1, 365), 30)作为日.
23.next_day函数 返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日
next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。
1 2 3 4 5 6 7
日 一 二 三 四 五 六
---------------------------------------------------------------
select (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from ddual
日期 返回的是天 然后 转换为ss
24,round[舍入到最接近的日期](day:舍入到最接近的星期日)
select sysdate S1,
round(sysdate) S2 ,
round(sysdate,'year') YEAR,
round(sysdate,'month') MONTH ,
round(sysdate,'day') DAY from dual
25,trunc[截断到最接近的日期,单位为天] ,返回的是日期类型
select sysdate S1,
trunc(sysdate) S2, //返回当前日期,无时分秒
trunc(sysdate,'year') YEAR, //返回当前年的1月1日,无时分秒
trunc(sysdate,'month') MONTH , //返回当前月的1日,无时分秒
trunc(sysdate,'day') DAY //返回当前星期的星期天,无时分秒
from dual
26,返回日期列表中最晚日期
select greatest('01-1月-04','04-1月-04','10-2月-04') from dual
27.计算时间差
注:oracle时间差是以天数为单位,所以换算成年月,日
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(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths 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 //时间差-秒
28.更新时间
注:oracle时间加减是以天数为单位,设改变量为n,所以换算成年月,日
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n*365,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-年
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),add_months(sysdate,n) as newTime from dual //改变时间-月
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-日
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-时
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-分
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-秒
29.查找月的第一天,最后一天
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;
每一个想法都是由大脑神经条件反射的产物
