Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

2 Answers
2

The fundamental issue is that TSQL cannot implicitly convert datetime (or integer or floating point) into character data types. It actually goes backwards, it attempts to implicitly convert the character data to the datetime (int/floating point value) based on the data type precedence rules. This is what your error message is telling you by the way, it couldn't convert the strings to datetime values. You have to explicitly ask it to mash values into character strings.

This example demonstrates the principal with integers as they are easier to understand expected values.

The value of the implicit conversion shows the @stringint is converted to integer first and then the + is treated as numeric addition and results in 8. The explicit conversion of @intint to a character data type results in the + sign treated as concatenation with the returned string of 35

But as indicated above, you really don't want to do that for a number of reasons, SQL Injection being one of them. It also makes your maintenance much harder when you're slicing and dicing a query string in TSQL.

A better approach is to parameterize your query and use the power of sp_executesql. A nice thing about the sp_executesql parameter is that you don't have to use all the parameters supplied. Depending on what you are truly trying to do, this may be beneficial.