我在宏函数里定义了两个参数STARTDATE和ENDDATE，T.DAYCODE为date类型，我想给这两个参数各自设置一个默认值，STARTDATE默认值为当前系统日期往前推7天，ENDDATE默认值为当前系统日期，验证SQL时报错，报错请看截图，我的数据库为Oracle，哪位大神帮解决一下，谢谢！
SELECT B.USERNAME,
T.USERCODE,
C.TYPENAME1 AS TASKTYPENAME,
C.TYPECODE1 AS TASKTYPECODE,
SUM(NVL(T.REALWORKTIME * (T.PROPORTION / 100), 0)) AS WORKTIME
FROM F_WORKREPORT T, D_USER B, D_TASKTYPE C
WHERE T.TASKTYPECODE = C.TASKTYPECODE
AND T.USERCODE = B.USERCODE
AND C.TASKTYPECODE <> '19'
AND (to_char(T.DAYCODE,'yyyy-mm-dd') BETWEEN #prompt('STARTDATE','string',to_char((sysdate-7),'yyyy-mm-dd'))# AND #prompt('ENDDATE','string',to_char((sysdate),'yyyy-mm-dd'))#)
GROUP BY
B.USERNAME,
T.USERCODE,
C.TYPENAME1,
C.TYPECODE1
另外，我把条件AND (to_char(T.DAYCODE,'yyyy-mm-dd') BETWEEN #prompt('STARTDATE','string',to_char((sysdate-7),'yyyy-mm-dd'))# AND #prompt('ENDDATE','string',to_char((sysdate),'yyyy-mm-dd'))#)改为N种写法：
1、T.DAYCODE BETWEEN to_date(#prompt('STARTDATE','string',to_char((sysdate-7),'yyyy-mm-dd'))#,'yyyy-mm-dd')
AND to_date(#prompt('ENDDATE','string',to_char((sysdate),'yyyy-mm-dd'))#,'yyyy-mm-dd')
2、T.DAYCODE BETWEEN #prompt('STARTDATE','date',to_date(to_char(sysdate-7,'yyyy-mm-dd'),'yyyy-mm-dd'))# AND #prompt('ENDDATE','date',to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'))#
还是验证不通过。
收起