月?要月干什么啊 如果生日字段包含年的话 where to_date(to_char(birthday,'mmdd'),'mmdd')-sysdate<=3
where TO_CHAR(birthday,'mm')=TO_CHAR(SYSDATE,'mm') and TO_CHAR(birthday,'dd')>TO_CHAR(SYSDATE,'dd') and TO_CHAR(birthday,'dd')<=TO_CHAR(SYSDATE+3,'dd')
参考scott模式中中emp表举例如下:scott@ORCL10G> SELECT SYSDATE FROM dual;SYSDATE -------------- 10-9月 -09scott@ORCL10G> scott@ORCL10G> SELECT * FROM emp WHERE TO_CHAR(hiredate,'mm')=TO_CHAR(SYSDATE,'mm'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- - 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30scott@ORCL10G> scott@ORCL10G> SELECT * FROM emp 2 WHERE TO_CHAR(hiredate,'mm')=TO_CHAR(SYSDATE,'mm') 3 AND TO_CHAR(hiredate,'dd')>TO_CHAR(SYSDATE,'dd') 4 AND TO_CHAR(hiredate,'dd')<=TO_CHAR(SYSDATE+17,'dd');未选定行scott@ORCL10G> scott@ORCL10G> scott@ORCL10G> SELECT * FROM emp 2 WHERE TO_CHAR(hiredate,'mm')=TO_CHAR(SYSDATE,'mm') 3 AND TO_CHAR(hiredate,'dd')>TO_CHAR(SYSDATE,'dd') 4 AND TO_CHAR(hiredate,'dd')<=TO_CHAR(SYSDATE+18,'dd'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- - 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
-- 方法一: WHERE TO_CHAR(hiredate,'mm')=TO_CHAR(SYSDATE,'mm') AND TO_CHAR(hiredate,'dd')>TO_CHAR(SYSDATE,'dd') AND TO_CHAR(hiredate,'dd')<=TO_CHAR(SYSDATE+18,'dd'); -- 方法二: WHERE TO_CHAR(hiredate,'mmdd') BETWEEN TO_CHAR(SYSDATE,'mmdd')+1 AND TO_CHAR(SYSDATE,'mmdd')+3;
---计算近三天将过生日的员工 ---正确方法一: WHERE TO_CHAR(birthday,'yyyy-mm-dd') BETWEEN TO_CHAR(birthday,'yyyy-')||TO_CHAR(SYSDATE+1,'mm-dd') AND TO_CHAR(birthday,'yyyy-')||TO_CHAR(SYSDATE+3,'mm-dd');---正确方法二: WHERE TO_CHAR(SYSDATE,'yyyy-')||TO_CHAR(birthday,'mm-dd') BETWEEN TO_CHAR(SYSDATE+1,'yyyy-mm-dd') AND TO_CHAR(SYSDATE+3,'yyyy-mm-dd');
---计算未来三天将过生日的员工 ---正确方法一(简洁版): WHERE TO_CHAR(birthday,'mm-dd') BETWEEN TO_CHAR(SYSDATE+1,'mm-dd') AND TO_CHAR(SYSDATE+3,'mm-dd');---正确方法二: WHERE TO_CHAR(birthday,'mm-dd') BETWEEN TO_CHAR(SYSDATE+1,'mm-dd') AND TO_CHAR(SYSDATE+3,'mm-dd');
加个条件
where birthday-sysdate<=3
如果生日字段包含年的话
where to_date(to_char(birthday,'mmdd'),'mmdd')-sysdate<=3
and TO_CHAR(birthday,'dd')>TO_CHAR(SYSDATE,'dd')
and TO_CHAR(birthday,'dd')<=TO_CHAR(SYSDATE+3,'dd')
--------------
10-9月 -09scott@ORCL10G>
scott@ORCL10G> SELECT * FROM emp WHERE TO_CHAR(hiredate,'mm')=TO_CHAR(SYSDATE,'mm'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- -
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30scott@ORCL10G>
scott@ORCL10G> SELECT * FROM emp
2 WHERE TO_CHAR(hiredate,'mm')=TO_CHAR(SYSDATE,'mm')
3 AND TO_CHAR(hiredate,'dd')>TO_CHAR(SYSDATE,'dd')
4 AND TO_CHAR(hiredate,'dd')<=TO_CHAR(SYSDATE+17,'dd');未选定行scott@ORCL10G>
scott@ORCL10G>
scott@ORCL10G> SELECT * FROM emp
2 WHERE TO_CHAR(hiredate,'mm')=TO_CHAR(SYSDATE,'mm')
3 AND TO_CHAR(hiredate,'dd')>TO_CHAR(SYSDATE,'dd')
4 AND TO_CHAR(hiredate,'dd')<=TO_CHAR(SYSDATE+18,'dd'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- -
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
WHERE TO_CHAR(hiredate,'mm')=TO_CHAR(SYSDATE,'mm')
AND TO_CHAR(hiredate,'dd')>TO_CHAR(SYSDATE,'dd')
AND TO_CHAR(hiredate,'dd')<=TO_CHAR(SYSDATE+18,'dd');
-- 方法二:
WHERE TO_CHAR(hiredate,'mmdd') BETWEEN TO_CHAR(SYSDATE,'mmdd')+1 AND TO_CHAR(SYSDATE,'mmdd')+3;
---正确方法一:
WHERE TO_CHAR(birthday,'yyyy-mm-dd') BETWEEN TO_CHAR(birthday,'yyyy-')||TO_CHAR(SYSDATE+1,'mm-dd')
AND TO_CHAR(birthday,'yyyy-')||TO_CHAR(SYSDATE+3,'mm-dd');---正确方法二:
WHERE TO_CHAR(SYSDATE,'yyyy-')||TO_CHAR(birthday,'mm-dd') BETWEEN TO_CHAR(SYSDATE+1,'yyyy-mm-dd')
AND TO_CHAR(SYSDATE+3,'yyyy-mm-dd');
---正确方法一(简洁版):
WHERE TO_CHAR(birthday,'mm-dd') BETWEEN TO_CHAR(SYSDATE+1,'mm-dd')
AND TO_CHAR(SYSDATE+3,'mm-dd');---正确方法二:
WHERE TO_CHAR(birthday,'mm-dd') BETWEEN TO_CHAR(SYSDATE+1,'mm-dd')
AND TO_CHAR(SYSDATE+3,'mm-dd');