查询出生日期为最近一个星期的sql还真不好写！日期是从今天向后推算.在线苦等......

Question

select * from tbname 
where abs(to_date(to_char(sysdate,'yyyy')||substr(coldate,5),'yyyy-mm-dd')-sysdate)<=7;

Answer 1

select * from table_name  where substr(出生日期字段,6,5)>= subsr(to_char(sysdate,'yyyy-mm-dd'),6,5)  and substr(出生日期字段,6,5)<= subsr(to_char((NEXT_DAY(sysdate,'Sunday'),'yyyy-mm-dd'),6,5)

Answer 2

14:06:03 SQL> select  * from ff;NAME                 BIRTHDAY
-------------------- --------------------
ff                   1981-09-24
ff                   2006-02-27已用时间:  00: 00: 00.00
14:06:05 SQL> select * from ff where to_date(birthday,'yyyy-mm-dd') < sysdate -7;NAME                 BIRTHDAY
-------------------- --------------------
ff                   1981-09-24已用时间:  00: 00: 00.00
14:06:41 SQL> select * from ff where to_date(birthday,'yyyy-mm-dd') > sysdate -7;NAME                 BIRTHDAY
-------------------- --------------------
ff                   2006-02-27已用时间:  00: 00: 00.00

Answer 3

无论如何也得： >= sysdate+7

Answer 4

select * from test where to_date(substr(sysdate,0,4)||substr(&birthday,5),'yyyy-mm-dd') between trunc(sysdate) and trunc(sysdate)+7

Answer 5

SQL> select * from t_birthday;T_NAME               T_BIRTHDAY                                                 
-------------------- ----------                                                 
DUDU                 17-9月 -80                                                 
panpan               13-2月 -81                                                 
shasha               14-11月-82                                                 
mimi                 20-2月 -82                                                 
xx                   21-2月 -83                                                 
ss                   03-3月 -83                                                 
ss                   22-2月 -83                                                 
ss                   23-2月 -83                                                 
ss                   24-2月 -66                                                 
a                    02-3月 -99                                                 
b                    08-3月 -00                                                 T_NAME               T_BIRTHDAY                                                 
-------------------- ----------                                                 
c                    09-3月 -61                                                 已选择12行。SQL>  select sysdate from dual;SYSDATE                                                                         
----------                                                                      
01-3月 -06                                                                      SQL> select * from t_birthday
  2  where trunc(sysdate)-to_date(substr(to_char(t_birthday,'yyyy-mm-dd'),6,2)||'-'||substr(to_char(t_birthday,'yyyy-mm-dd'),9,2)||'-'||to_char(sysdate,'yyyy'),'mm-dd-yyyy') between -7 and -1;T_NAME               T_BIRTHDAY                                                 
-------------------- ----------                                                 
ss                   03-3月 -83                                                 
a                    02-3月 -99                                                 
b                    08-3月 -00

Answer 6

简化一下  select * from t_birthday 
where trunc(sysdate)-to_date(to_char(t_birthday,'mm-dd')||'-'||to_char(sysdate,'yyyy'),'mm-dd-yyyy') between -7 and -1

Answer 7

select * from t_birthday 
where t_birthday between NEXT_DAY(TRUNC(SYSDATE-7),'星期日') --这一周的第一天
and NEXT_DAY(TRUNC(SYSDATE),'星期六') --这一周的最后一天