SQL> create table test000(createtime date);Table createdSQL> insert into test000 values(sysdate);1 row insertedSQL> commit;Commit completeSQL> select * from test000;CREATETIME ----------- 12/15/2006SQL> 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;请把执行结果贴出来
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 clinic_reg_master where trunc(clinic_date,'d') = trunc(sysdate,'d');--星期一是第一天 select * from clinic_reg_master where trunc(clinic_date+1,'d') = trunc(sysdate+1,'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') 指定时间的本周一的前一天(周日)所在日期
-----------
12/15/2006SQL> select * from test000 where to_date(createtime)>=sysdate-7;CREATETIME
-----------
12/15/2006
where trunc(clinic_date,'d') = trunc(sysdate,'d');
--星期日是第一天
select * from clinic_reg_master
where trunc(clinic_date,'d') = trunc(sysdate,'d');--星期一是第一天
select * from clinic_reg_master
where trunc(clinic_date+1,'d') = trunc(sysdate+1,'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') 指定时间的本周一的前一天(周日)所在日期