QL> create table test000(createtime date);
Table created
SQL> insert into test000 values(sysdate);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test000;
CREATETIME
-----------
12/15/2006
SQL> select * from test000 where to_date(createtime)>=sysdate-7;
CREATETIME
-----------
12/15/2006
select * from clinic_reg_master where to_date(clinic_date)>=sysdate - '7'
select * from clinic_reg_master where to_char(clinic_date)>=sysdate-7 and to_char(clinic_date)<=sysdate;
select * from clinic_reg_master
where trunc(clinic_date,'d') = trunc(sysdate,'d');
select * from yourtable where TIME BETWEEN TRUNC(TIME,'IW') AND TRUNC(TIME) + 7
查询时间点所在周日到下周六的:
select * from yourtable where TIME BETWEEN TRUNC(TIME,'WW') AND TRUNC(TIME) + 7
说明:
TRUNC(TIME,'IW') 指定时间的本周一所在日期
TRUNC(TIME,'WW') 指定时间的本周一的前一天(周日)所在日期
Table created
SQL> insert into test000 values(sysdate);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test000;
CREATETIME
-----------
12/15/2006
SQL> select * from test000 where to_date(createtime)>=sysdate-7;
CREATETIME
-----------
12/15/2006
select * from clinic_reg_master where to_date(clinic_date)>=sysdate - '7'
select * from clinic_reg_master where to_char(clinic_date)>=sysdate-7 and to_char(clinic_date)<=sysdate;
select * from clinic_reg_master
where trunc(clinic_date,'d') = trunc(sysdate,'d');
select * from yourtable where TIME BETWEEN TRUNC(TIME,'IW') AND TRUNC(TIME) + 7
查询时间点所在周日到下周六的:
select * from yourtable where TIME BETWEEN TRUNC(TIME,'WW') AND TRUNC(TIME) + 7
说明:
TRUNC(TIME,'IW') 指定时间的本周一所在日期
TRUNC(TIME,'WW') 指定时间的本周一的前一天(周日)所在日期