select * from emp where to_date(sysdate,'yyyy-mm-dd')<to_date(dateField,'yyyy-mm-dd')
and to_date(dateField,'yyyy-mm-dd')<to_date(dateField,'yyyy-mm-dd')+10
好久没有写sql了。上面的差不多吧
and to_date(dateField,'yyyy-mm-dd')<to_date(dateField,'yyyy-mm-dd')+10
好久没有写sql了。上面的差不多吧
select *
from emp
where to_char(birthday,'mmdd')
between to_char(sysdate,'mmdd')
and to_char(sysdate+10,'mmdd');
from emp
where to_date('2004'||to_char(birthday,'mmdd'),'yyyymmdd')
between to_date('2004'||to_char(sysdate,'mmdd'),'yyyymmdd')
and to_date('2004'||to_char(sysdate+10,'mmdd'),'yyyymmdd');
这样应该可以,但是好累赘,不过我也还没想到更好的办法。如果对to_date('2004'||to_char(birthday,'mmdd'),'yyyymmdd') 建个FBI可能效率更高。
对了,使用2004是为了防止闰年
where trunc(birthday)>=trunc(sysdate)
and trunc(birthday)<=trunc(sysdate)+10
where trunc(to_date(to_char(sysdate,'YYYY')||to_char(birthday,'MMDD'),'YYYYMMDD'))>=trunc(sysdate)
and trunc(to_date(to_char(sysdate,'YYYY')||to_char(birthday,'MMDD'),'YYYYMMDD'))<=trunc(sysdate)+10
如05.1.1生日,今天是04.12.31的话用sysdate出来2004.1.1还是出不来。想了会好像不太会,楼主不如用个procedure来搞定好了,不用动脑,呵呵。。
select * from emp
where to_date(to_char(sysdate,'YYYY')||to_char(birthday,'MMDD'),'YYYYMMDD')
between trunc(sysdate) and trunc(sysdate)+10
or to_date(to_char(to_number(to_char(sysdate,'YYYY'))+1)||to_char(birthday,'MMDD'),'YYYYMMDD')
between trunc(sysdate) and trunc(sysdate)+10还不行或太烦就建个表出来,共一列number型10行。如temp_tbl(num(0,1,2,3,4,5,6,7,8,9))
select * from emp
where to_char(birthday,'mmdd') in
(select to_char(sysdate + num,'mmdd')) from temp_tbl)不想or不能建表的话,随便找个大于10行的表
select * from emp
where to_char(birthday,'mmdd') in
(select to_char(sysdate + rownum,'mmdd')) from emp where rownum < 10)
from emp
where to_char(birthday,'yyyymmdd')
between to_char(sysdate,'yyyymmdd')
and to_char(sysdate+10,'yyyymmdd')
这个总没问题吧
SELECT *
FROM emp
WHERE birthday > SYSDATE
AND birthday < SYSDATE + 10
SELECT *
FROM emp
WHERE birthday > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'),'YYYYMMDD')
AND birthday < TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'),'YYYYMMDD') + 10
select * from emp
where add_months(birthday,ceil(months_between(sysdate,birthday)/12)*12)
between sysdate and sysdate + 10
把生日加上距系统日期的年数(不足1年算1年),然后再和系统日期比
比如生日是2004/1/5,当前时期是2004/12/31,把生日加12个月到2005/1/5,再和12/31号比就应该没问题了