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.
DECLARE
@stringInt nvarchar(3)
, @intint int
SELECT
@stringInt = N'3'
, @intint = 5
SELECT
@stringInt + @intint AS implicit_conversion
, @stringInt + CAST(@intint AS nvarchar(5)) AS explicit_conversion
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
implicit_conversion explicit_conversion
------------------- -------------------
8 35
To solve your supplied problem, you must explicitly cast your datetime values to a character type so the query string can be concatenated as expected.
set @query='SELECT [col1]
FROM [Table1]
WHERE ([col2] BETWEEN ''' + CONVERT(nvarchar(24), @FromDate, 121) +''' AND'''+ CONVERT(nvarchar(24), @ToDate, 121) +''')'
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.
Sample table and data
CREATE TABLE
dbo.table1
(col1 int, col2 datetime)
INSERT INTO
dbo.table1
SELECT
3, '2009-04-06'
UNION ALL SELECT
1, '2001-09-11'
Demo of using parameters
DECLARE
@FromDate datetime,
@ToDate datetime
SELECT
@FromDate = '2005-03-17'
, @ToDate = current_timestamp
DECLARE
@query nvarchar(max)
SET @query = N'
SELECT [col1]
FROM [Table1]
WHERE ([col2] BETWEEN @start AND @end)'
-- gratuitous use of parameter assignment here
-- could just as easily used @FromDate and @ToDate
-- in the @query and the parameter list
EXECUTE sp_executesql
@query
, N'@start datetime, @end datetime'
, @start = @FromDate
, @end = @ToDate
Results
col1
3
Best Answer
You are correct. CASE Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression. Data type precedence comes into play here.
You'd have to convert your column to a
varchar.DB FIDDLE
You can reference the
CAST and CONVERTdocumentation for other date/time formats.