I’ve heard that SQL Server developers happen to have problems with CONVERT function when try to obtain a value of datetime type. Recently, I’ve had that problem during a Data Transformation Services (DTS) package implementation.
One of the task in that package was “Transform Data”, which transferred data between a source and destination. To retrieve that data, I used SQL query within which I needed to add a number of milliseconds to a given date. The following snippet shows what I really wanted to execute:
dateadd (millisecond, ?, convert(datetime,?))
In abovementioned snippet, both question marks (?) are different parameters used in that query. The former is sINTEGER (the number of millseconds) whereas the latter is the date kept as VARCHAR. The latter parameter needs convertion to DATETIME.
Unfortunately, I could’t achieve what I really wanted. When I put the abovemeantioned snippet in the query in “Transform Data Task Properties” window, I couldn’t even save it – got the following error: “… argument data type datetime is invalid for argument 2 of dateadd function”.
I spent some time analyzing what was going on. I ran thas query in Query Analyzer with hardcoded values and it was ok. Finally, I used Profiler tool to see what exactly happened when the task (and consequently that query) was run. That gave me the explanation: the second parameter was unnecessary converted by the engine which executed that package. After that convertion, the value of that parameter was wrong and finally was converted second time to a wrong DATE value.
The solution to that problem is quite easy: the described query can be put in a stored procedure which is executed during the transformation. The SQL engine had difficulties defining the data types of the parameters. Luckily, in a stored procedure, the data types are explicitly defined.
