select to_date('20091230','yyyymmdd')+7 from dual; 好像也不麻烦
--就这样减啊 birthday - trunc(sysdate,'dd')<=7
有个笨办法就是,to_date('20091230','yyyymmdd')+7后 to_date(to_date('20091230','yyyymmdd')+7,'mm')和生日月比较 and to_date(to_date('20091230','yyyymmdd')+7,'dd')和生日日比较
trunc(sr) - trunc(sysdate) between 0 and 7
想了办法,不过遇到闰年会有误差,闰年多了误差也大了,你先试试看,我再想想 select * from table where 365-mod(trunc(sysdate)-trunc(SR),365)<=7
create table table1(sr date);insert into table1 select to_date('1986-12-25','yyyy-mm-dd') from dual union all select to_date('1986-02-01','yyyy-mm-dd') from dual union all select to_date('1986-01-29','yyyy-mm-dd') from dual union all select to_date('1986-02-05','yyyy-mm-dd') from dualSQL> select * from table1;
SR ----------- 1986-12-25 1986-2-1 1986-1-29 1986-2-5
SQL> SQL> select sysdate,sr 2 from table1 3 where sysdate<=7 + 4 case when (extract(month from sr)=12 and extract(day from sr)>=31-7) then 5 to_date(to_char(to_number(to_char(sysdate,'yyyy'))-1)||to_char(sr,'mmdd'),'yyyy-mm-dd') 6 else 7 to_date(to_char(sysdate,'yyyy')||to_char(sr,'mmdd'),'yyyy-mm-dd') 8 end 9 and 10 sysdate >= 11 case when (extract(month from sr)=12 and extract(day from sr)>=31-7) then 12 to_date(to_char(to_number(to_char(sysdate,'yyyy'))-1)||to_char(sr,'mmdd'),'yyyy-mm-dd') 13 else 14 to_date(to_char(sysdate,'yyyy')||to_char(sr,'mmdd'),'yyyy-mm-dd') 15 end 16 ;
假设用户的生日是1959.01.02 当前日期是2009.12.28 你怎么写where 条件 数据库的生日日期纪录是的1959.01.02 你当前日期+7就是2009.12.28-2010.1.4 怎么写比较条件呢,不可能直接写 sr between sysdate and sysdate+7 吧
SELECT TO_CHAR(NEXT_DAY (SYSDATE, TO_CHAR(SYSDATE,'DAY')),'YYYYMMDD') FROM DUAL;
select * from table_name where to_char(SR,'mm-dd') in( select to_char(sysdate+rownum,'mm-dd') from dual connect by rownum<=7 );
修正一下,之前的逻辑有问题,如下: select sysdate,sr from table1 where sysdate<=7 + case when (to_date(to_char(sysdate,'yyyy')||to_char(sr,'mmdd'),'yyyy-mm-dd')>sysdate) then to_date(to_char(to_number(to_char(sysdate,'yyyy'))-1)||to_char(sr,'mmdd'),'yyyy-mm-dd') else to_date(to_char(sysdate,'yyyy')||to_char(sr,'mmdd'),'yyyy-mm-dd') end and sysdate>= case when (to_date(to_char(sysdate,'yyyy')||to_char(sr,'mmdd'),'yyyy-mm-dd')>sysdate) then to_date(to_char(to_number(to_char(sysdate,'yyyy'))-1)||to_char(sr,'mmdd'),'yyyy-mm-dd') else to_date(to_char(sysdate,'yyyy')||to_char(sr,'mmdd'),'yyyy-mm-dd') end
with tt as ( select to_date('19820505','yyyymmdd') sr from dual union all select to_date('19811225','yyyymmdd') sr from dual union all select to_date('19811230','yyyymmdd') sr from dual union all select to_date('19820102','yyyymmdd') sr from dual ) select * from tt where to_char(tt.sr, 'mmdd') in (select to_char(to_date('20091228', 'yyyymmdd') + rownum, 'mmdd') from dual connect by rownum <= 7)
SR --------- 30-DEC-81 02-JAN-82
WITH a AS (SELECT 1 ID, TO_DATE ('19650102', 'yyyymmdd') sr FROM DUAL UNION ALL SELECT 2 ID, TO_DATE ('19591202', 'yyyymmdd') sr FROM DUAL UNION ALL SELECT 3 ID, TO_DATE ('19590202', 'yyyymmdd') sr FROM DUAL UNION ALL SELECT 4 ID, TO_DATE ('19590214', 'yyyymmdd') sr FROM DUAL UNION ALL SELECT 5 ID, TO_DATE ('19590209', 'yyyymmdd') sr FROM DUAL UNION ALL SELECT 6 ID, TO_DATE ('19590204', 'yyyymmdd') sr FROM DUAL UNION ALL SELECT 7 ID, TO_DATE ('19590211', 'yyyymmdd') sr FROM DUAL)SELECT * FROM a WHERE CASE WHEN TO_NUMBER (TO_CHAR (sr, 'mm')) - TO_NUMBER (TO_CHAR (SYSDATE, 'mm')) > 0 THEN TO_DATE ( TO_CHAR (ADD_MONTHS (SYSDATE, 12), 'yyyy') || TO_CHAR (sr, 'mmdd'), 'yyyymmdd' ) ELSE TO_DATE (TO_CHAR (SYSDATE, 'yyyy') || TO_CHAR (sr, 'mmdd'), 'yyyymmdd' ) END - SYSDATE < 7 AND CASE WHEN TO_NUMBER (TO_CHAR (sr, 'mm')) - TO_NUMBER (TO_CHAR (SYSDATE, 'mm')) > 0 THEN TO_DATE ( TO_CHAR (ADD_MONTHS (SYSDATE, 12), 'yyyy') || TO_CHAR (sr, 'mmdd'), 'yyyymmdd' ) ELSE TO_DATE (TO_CHAR (SYSDATE, 'yyyy') || TO_CHAR (sr, 'mmdd'), 'yyyymmdd' ) END - SYSDATE >= 0 ID SR5 1959/2/9 7 1959/2/11
SQL> select * from table1;
SR ----------- 1986-12-25 1986-2-1 1986-1-29 1986-2-5 1986-1-1
SQL> SQL> select * 2 from table1 3 where to_char(SR,'mm-dd') in( 4 select to_char(sysdate+rownum,'mm-dd') 5 from dual 6 connect by rownum<=7 );
好像也不麻烦
--就这样减啊
birthday - trunc(sysdate,'dd')<=7
to_date(to_date('20091230','yyyymmdd')+7,'mm')和生日月比较
and
to_date(to_date('20091230','yyyymmdd')+7,'dd')和生日日比较
select *
from table
where 365-mod(trunc(sysdate)-trunc(SR),365)<=7
create table table1(sr date);insert into table1
select to_date('1986-12-25','yyyy-mm-dd') from dual
union all
select to_date('1986-02-01','yyyy-mm-dd') from dual
union all
select to_date('1986-01-29','yyyy-mm-dd') from dual
union all
select to_date('1986-02-05','yyyy-mm-dd') from dualSQL> select * from table1;
SR
-----------
1986-12-25
1986-2-1
1986-1-29
1986-2-5
SQL>
SQL> select sysdate,sr
2 from table1
3 where sysdate<=7 +
4 case when (extract(month from sr)=12 and extract(day from sr)>=31-7) then
5 to_date(to_char(to_number(to_char(sysdate,'yyyy'))-1)||to_char(sr,'mmdd'),'yyyy-mm-dd')
6 else
7 to_date(to_char(sysdate,'yyyy')||to_char(sr,'mmdd'),'yyyy-mm-dd')
8 end
9 and
10 sysdate >=
11 case when (extract(month from sr)=12 and extract(day from sr)>=31-7) then
12 to_date(to_char(to_number(to_char(sysdate,'yyyy'))-1)||to_char(sr,'mmdd'),'yyyy-mm-dd')
13 else
14 to_date(to_char(sysdate,'yyyy')||to_char(sr,'mmdd'),'yyyy-mm-dd')
15 end
16 ;
SYSDATE SR
----------- -----------
2010-2-4 16 1986-2-1
2010-2-4 16 1986-1-29
考虑了生日是年末的情况,笨方法,期待牛X的sql
当前日期是2009.12.28
你怎么写where 条件
数据库的生日日期纪录是的1959.01.02
你当前日期+7就是2009.12.28-2010.1.4
怎么写比较条件呢,不可能直接写 sr between sysdate and sysdate+7 吧
select *
from table_name
where to_char(SR,'mm-dd') in(
select to_char(sysdate+rownum,'mm-dd')
from dual
connect by rownum<=7 );
修正一下,之前的逻辑有问题,如下:
select sysdate,sr
from table1
where sysdate<=7 +
case when (to_date(to_char(sysdate,'yyyy')||to_char(sr,'mmdd'),'yyyy-mm-dd')>sysdate) then
to_date(to_char(to_number(to_char(sysdate,'yyyy'))-1)||to_char(sr,'mmdd'),'yyyy-mm-dd')
else
to_date(to_char(sysdate,'yyyy')||to_char(sr,'mmdd'),'yyyy-mm-dd')
end
and sysdate>=
case when (to_date(to_char(sysdate,'yyyy')||to_char(sr,'mmdd'),'yyyy-mm-dd')>sysdate) then
to_date(to_char(to_number(to_char(sysdate,'yyyy'))-1)||to_char(sr,'mmdd'),'yyyy-mm-dd')
else
to_date(to_char(sysdate,'yyyy')||to_char(sr,'mmdd'),'yyyy-mm-dd')
end
with
tt as
(
select to_date('19820505','yyyymmdd') sr from dual
union all
select to_date('19811225','yyyymmdd') sr from dual
union all
select to_date('19811230','yyyymmdd') sr from dual
union all
select to_date('19820102','yyyymmdd') sr from dual
)
select * from tt
where to_char(tt.sr, 'mmdd') in
(select to_char(to_date('20091228', 'yyyymmdd') + rownum, 'mmdd') from dual
connect by rownum <= 7)
---------
30-DEC-81
02-JAN-82
WITH a AS
(SELECT 1 ID, TO_DATE ('19650102', 'yyyymmdd') sr
FROM DUAL
UNION ALL
SELECT 2 ID, TO_DATE ('19591202', 'yyyymmdd') sr
FROM DUAL
UNION ALL
SELECT 3 ID, TO_DATE ('19590202', 'yyyymmdd') sr
FROM DUAL
UNION ALL
SELECT 4 ID, TO_DATE ('19590214', 'yyyymmdd') sr
FROM DUAL
UNION ALL
SELECT 5 ID, TO_DATE ('19590209', 'yyyymmdd') sr
FROM DUAL
UNION ALL
SELECT 6 ID, TO_DATE ('19590204', 'yyyymmdd') sr
FROM DUAL
UNION ALL
SELECT 7 ID, TO_DATE ('19590211', 'yyyymmdd') sr
FROM DUAL)SELECT *
FROM a
WHERE CASE
WHEN TO_NUMBER (TO_CHAR (sr, 'mm'))
- TO_NUMBER (TO_CHAR (SYSDATE, 'mm')) > 0
THEN TO_DATE ( TO_CHAR (ADD_MONTHS (SYSDATE, 12), 'yyyy')
|| TO_CHAR (sr, 'mmdd'),
'yyyymmdd'
)
ELSE TO_DATE (TO_CHAR (SYSDATE, 'yyyy') || TO_CHAR (sr, 'mmdd'),
'yyyymmdd'
)
END
- SYSDATE < 7
AND CASE
WHEN TO_NUMBER (TO_CHAR (sr, 'mm'))
- TO_NUMBER (TO_CHAR (SYSDATE, 'mm')) > 0
THEN TO_DATE ( TO_CHAR (ADD_MONTHS (SYSDATE, 12), 'yyyy')
|| TO_CHAR (sr, 'mmdd'),
'yyyymmdd'
)
ELSE TO_DATE (TO_CHAR (SYSDATE, 'yyyy') || TO_CHAR (sr, 'mmdd'),
'yyyymmdd'
)
END
- SYSDATE >= 0
ID SR5 1959/2/9
7 1959/2/11
SR
-----------
1986-12-25
1986-2-1
1986-1-29
1986-2-5
1986-1-1
SQL>
SQL> select *
2 from table1
3 where to_char(SR,'mm-dd') in(
4 select to_char(sysdate+rownum,'mm-dd')
5 from dual
6 connect by rownum<=7 );
SR
-----------
SQL>
??
SYSDATE
-----------
2010-1-1 16
为什么对于1986-12-25
这些不考虑年月,与系统时间刚好相差7天的日期查不出来