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
Write your query this way. It includes a better test for integers and dumps ISNUMERIC that returns 1 for '-.', for example.
SELECT
zip,
coordinates
FROM (
SELECT
CASE WHEN ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%' THEN
CONVERT(int, [ZIP5]) END zip,
CONVERT(varchar(max), geom) AS coordinates
FROM
[SpatialData].[dbo].[zip5]
WHERE
ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%'
) AS t1
WHERE
zip >= 85000 AND
zip < 86000
See this Connect item
SQL Server is free to evaluate the WHERE/SELECT clause in the order that it decides is optimized. A view or derived table is not materialized can easily be expanded into, from the outer query.
What SQL Server is compiling is really a query that looks like this
SELECT
CONVERT(int, [ZIP5]) AS zip,
CONVERT(varchar(max), geom) AS coordinates
FROM
[SpatialData].[dbo].[zip5]
WHERE
ISNUMERIC([ZIP5]) = 1
AND CONVERT(int, [ZIP5]) >= 85000
AND CONVERT(int, [ZIP5]) < 86000
You can inspect the query plan of your original, but my guess from looking at the structure is that the WHERE clause uses the expression CONVERT(int, [ZIP5])
twice, so it makes sense to streamline the resolution (calculation to a result) of the expression in the process of retrieving data from the table. This puts the processing of the SELECT clause before the WHERE, so your ISNUMERIC() = 1
never got a chance to filter the bad eggs.
It is by-design
.
Best Answer
You have data in
approxstartdat
e column that cannot be converted todatetime
, it’s bad data, chances are if you saw what that data is you wouldn’t know how to convert it to a datetime either.If you are using SQL Server, take a look at
try_convert
which returns null if the value cannot be converted. You can use this function to find the values in your table that cannot be converted and then fix them or remove them.