DSXchange

I am trying to pass a date to the ODBC user defined sql delete statement listed below from my job but it is deleting the entire table instead of only the records that meet the date criteria. If I hard code the date of 10/01/2010 in the delete statement, it works fine. Any assistance would be greatly appreciated. I have my job param defined as a Date but have also tried string. The Field in the SQL table is defined as datetime.

DELETE UHCDATA.COMORBIDITY_DEV
FROM
(SELECT C.RECORDID, C.UNIVCODE, C.DATABASEID
FROM UHCDATA.ENCOUNTER A, CAPMGT.DATES B, UHCDATA.COMORBIDITY_DEV C
WHERE A.RECORD_ID = C.RECORDID
AND A.UNIV_CODE = C.UNIVCODE
AND A.DATABASE_ID = C.DATABASEID
AND A.DISCHARGEDATE = B.DATE_ID
AND B.FULL_DATE >= #Start_Date#) D
WHERE
D.RECORDID = UHCDATA.COMORBIDITY_DEV.RECORDID
AND D.UNIVCODE = UHCDATA.COMORBIDITY_DEV.UNIVCODE
AND D.DATABASEID = UHCDATA.COMORBIDITY_DEV.DATABASEID

Thank you.

Try like , Pass date param as string and then chagne your query like this. to_date(#Start_Date#,'YYYYMMDD')

AND B.FULL_DATE >= "#Start_Date#")

Try putting double quotes around the parameter.

AND B.FULL_DATE >= "#Start_Date#")

Try putting double quotes around the parameter.

Single quotes, surely?

ray.wurlod wrote:Single quotes, surely?

Yep, the single quotes worked. Thanks everyone!