SQL Server Performance Forum – Threads Archive

Replace ‘n’ in dateadd with global variable

Hi I have SQL in a ‘Transform Data Task’ like this Select * from
from Table1
where EffectiveDate > ?
and DateLastUpdated > DATEADD(day, -3, ?) … and I’ve specified GlobalVariable ‘EffectiveDate’ as Parameter 1 and 2. This works ok – and extracts all data updated within 3 days of ‘EffectiveDate’ I’d like to parameterise the ‘3’, so I added a new GlobalVariable called NumberofDays, and changed the SQL to Select * from
from Table1
where EffectiveDate > ?
and DateLastUpdated > DATEADD(day, -?, ?) DTS allows me to specify NumberofDays as Parameter 2, but when I click OK to close the properties, I get a pop-up box saying Error Source: Microsoft OLE DB provider for SQL Server Error Description: Deferred prepare could not be completed
Statement(s) could not be prepared.
Invalid operator for data type. Operator equals minus, type = datetime. This is despite the fact that I defined ‘NumberofDays’ as an unsigned Integer (and NOT a datetime) field. If I remove the ‘minus’ sign I get this
Error Source: Microsoft OLE DB provider for SQL Server Error Description: Deferred prepare could not be completed
Statement(s) could not be prepared.
Argument date type datetime is invalid for argument 2 of dateadd function So it seems to think that ‘NumberofDays’ is a datetime field, which it’s NOT … any ideas? Thanks
Bennie
Sydney

---

See if you find any scripts here
www.sqldts.com Madhivanan Failing to plan is Planning to fail

---

]]>