SELECT NSR.NSRBM NSRBM,
NSR.NSR_MC NSRMC,
sum(ZJ.SJ_JE) JAN,
NULL FEB,
NULL MAR,
NULL APR,
NULL MAY,
NULL JUN,
NULL JUL,
NULL AUG,
NULL SEP,
NULL OCT,
NULL NOV,
NULL DEC
FROM T_SFZS_ZJJKMX ZJ,
T_DJ_NSRXX NSR,
T_DM_GY_SWJG GLJG,
T_DM_GY_SWJG QXJ
WHERE NSR.NSRNBM(+) = ZJ.NSRNBM
AND ZJ.GLJG_DM = GLJG.SWJG_DM
AND GLJG.SJSWJG_DM = QXJ.SWJG_DM
AND QXJ.SWJG_DM = zj.hsjg_dm
AND ZJ.ZSXM_DM = '66'
and to_date((select extract(year from sysdate) from dual),'yyyy') = to_date(zj.rk_rq,'yyyy') and zj.rk_rq >= to_date('01-01', 'mm-dd')
and zj.rk_rq <= to_date('01-31', 'mm-dd')
/* and [qxj.sjswjg_dm=@sj@]
AND [ZJ.HSJG_DM=@QXJ@]
AND [NSR.GLJG_DM=@GLJG@]*/
GROUP BY NSR.NSRBM,NSR.NSR_MC现在的问题是我要让and to_date((select extract(year from sysdate) from dual),'yyyy') = to_date(zj.rk_rq,'yyyy') and 不报错,ZJ.RK_RQ为日期型,格式为‘yyyy-mm-dd’,有什么好的办法解决。
NSR.NSR_MC NSRMC,
sum(ZJ.SJ_JE) JAN,
NULL FEB,
NULL MAR,
NULL APR,
NULL MAY,
NULL JUN,
NULL JUL,
NULL AUG,
NULL SEP,
NULL OCT,
NULL NOV,
NULL DEC
FROM T_SFZS_ZJJKMX ZJ,
T_DJ_NSRXX NSR,
T_DM_GY_SWJG GLJG,
T_DM_GY_SWJG QXJ
WHERE NSR.NSRNBM(+) = ZJ.NSRNBM
AND ZJ.GLJG_DM = GLJG.SWJG_DM
AND GLJG.SJSWJG_DM = QXJ.SWJG_DM
AND QXJ.SWJG_DM = zj.hsjg_dm
AND ZJ.ZSXM_DM = '66'
and to_date((select extract(year from sysdate) from dual),'yyyy') = to_date(zj.rk_rq,'yyyy') and zj.rk_rq >= to_date('01-01', 'mm-dd')
and zj.rk_rq <= to_date('01-31', 'mm-dd')
/* and [qxj.sjswjg_dm=@sj@]
AND [ZJ.HSJG_DM=@QXJ@]
AND [NSR.GLJG_DM=@GLJG@]*/
GROUP BY NSR.NSRBM,NSR.NSR_MC现在的问题是我要让and to_date((select extract(year from sysdate) from dual),'yyyy') = to_date(zj.rk_rq,'yyyy') and 不报错,ZJ.RK_RQ为日期型,格式为‘yyyy-mm-dd’,有什么好的办法解决。
lz的条件是 系统日期的年份和目标字段的年份相同吧,试试这样写and to_char((select sysdate from dual),'yyyy') = to_char(zj.rk_rq,'yyyy') and
改为
and to_char(sysdate,'yyyy')=substr(zj.rk_rq,1,4)
SQL> select * from emp;EMPNO ENAME
-------------------- ------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
MGR HIREDATE SAL COMM DEPTNO
-------------------- ---------- --------- --------- --------------------
7782 CLARK
MANGER
7839 09-6月 -81 2450 107782 CLARK
MANGER
7839 20-4月 -09 2450 10
SQL> select count(*) from emp where to_char((select sysdate from dual),'yyyy')=to_char(hiredate,'yyyy'); COUNT(*)
---------
1