Conversion failed when converting date and/or time from character string - SQL Server error

Question

I'm trying to dynamically select tables from my database based on the table name, which in turn is based on the date of creation. For example, the tables might be called 'tableA20110305', or 'tableB20110305', indicating that the tables were created on 05 March 2011.

I'm trying to write a query that will select all tables named thus, created before a certain cutoff date (1 year ago), and concatenate them into DROP TABLE command statements in a table variable. The select statement looks like this.

DECLARE @cutoffDate datetime = CONVERT(DATETIME, DATEADD(YEAR,-1,GETDATE()), 112)

SELECT 'DROP TABLE "' + TABLE_NAME + '"' AS 'Command'
    FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_NAME LIKE 'tableA%' OR TABLE_NAME LIKE 'tableB%')
    AND (CONVERT(DATETIME, SUBSTRING(TABLE_NAME, 7, 8), 112) < @cutoffDate)
    ORDER BY Command DESC

However, when I execute this SQL, I'm seeing the following error:

Msg 241, Level 16, State 1, Line 14 Conversion failed when converting date and/or time from character string.

BUT... if I execute the following SQL statement, I see no error and get date returned as expected:

SELECT CONVERT(DATETIME, SUBSTRING('tableA20110305', 7, 8), 112)

I don't understand why these queries are not returning the same result or where this error is coming from. I'd very much appreciate any insights..

Answer 1

This explains this behavior very well. Taken from 70-461: Querying Microsoft SQL Server 2012:

WHERE propertytype = 'INT' AND CAST(propertyval AS INT) > 10 

Suppose that the table being queried holds different property values. The propertytype column represents the type of the property (an INT, a DATE, and so on), and the propertyval column holds the value in a character string. When propertytype is 'INT', the value in propertyval is convertible to INT; otherwise, not necessarily.

Some assume that unless precedence rules dictate otherwise, predicates will be evaluated from left to right, and that short circuiting will take place when possible. In other words, if the first predicate propertytype = 'INT' evaluates to false, SQL Server won’t evaluate the second predicate CAST(propertyval AS INT) > 10 because the result is already known. Based on this assumption, the expectation is that the query should never fail trying to convert something that isn’t convertible.

The reality, though, is different. SQL Server does internally support a short-circuit concept; however, due to the all-at-once concept in the language, it is not necessarily going to evaluate the expressions in left-to-right order. It could decide, based on cost-related reasons, to start with the second expression, and then if the second expression evaluates to true, to evaluate the first expression as well. This means that if there are rows in the table where propertytype is different than 'INT', and in those rows propertyval isn’t convertible to INT, the query can fail due to a conversion error.

And in your case engine decides first to do filter by dates part and it fails.

And there can be several workaround:

1. Use TRY_CAST instead(supported from SQL Server 2012)

2. First select all tables which are like 'tableA%' OR TABLE_NAME LIKE 'tableB%' into some temp table and then do another filter (CONVERT(DATETIME, SUBSTRING(TABLE_NAME, 7, 8), 112) < @cutoffDate)

Answer 2

DECLARE @cutoffDate Varchar(8);   --<-- use varchar here not datetime since you YYYYMMDD
SET @cutoffDate  = CONVERT(Varchar(8), DATEADD(YEAR,-1,GETDATE()), 112)

SELECT 'DROP TABLE '+ QUOTENAME(TABLE_SCHEMA) +'.' + QUOTENAME(TABLE_NAME)  AS [Command]
From (
    Select TABLE_SCHEMA , TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_NAME LIKE 'tableA%' OR TABLE_NAME LIKE 'tableB%')
    AND ISDATE(SUBSTRING(TABLE_NAME, 7, 8)) = 1 
    ) A
Where (CONVERT(DATETIME, SUBSTRING(TABLE_NAME, 7, 8)) < @cutoffDate)
ORDER BY Command DESC

Adding ISDATE(SUBSTRING(TABLE_NAME, 7, 8)) = 1 to your where clause will only bring back the results which has a proper date value in its name hence converting it to date/datetime should work.

Answer 3

Well , as mentioned in the comments you probably have other tables in your database that does not follow the same format as tableA<DateFormat> , so you need to try to filter only them .

You can use ISDATE combined with CASE EXPRESSION to make sure the SUBSTRING is indeed in a date format:

DECLARE @cutoffDate datetime = CONVERT(DATETIME, DATEADD(YEAR,-1,GETDATE()), 112)

SELECT 'DROP TABLE "' + TABLE_NAME + '"' AS 'Command'
    FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_NAME LIKE 'tableA%' OR TABLE_NAME LIKE 'tableB%')
    AND CASE WHEN ISDATE(SUBSTRING(TABLE_NAME, 7, 8)) = 1 
             THEN (CONVERT(DATETIME, SUBSTRING(TABLE_NAME, 7, 8), 112)
             ELSE getdate()
        END < @cutoffDate
    ORDER BY Command DESC

Answer 4

The problem is that some table matches the condition but does not have the prescribed format. In SQL Server 2012+, you can use try_convert():

SELECT 'DROP TABLE "' + TABLE_NAME + '"' AS 'Command'
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_NAME LIKE 'tableA%' OR TABLE_NAME LIKE 'tableB%') AND
      (TRY_CONVERT(DATETIME, SUBSTRING(TABLE_NAME, 7, 8), 112) < @cutoffDate)
ORDER BY Command DESC;

In earlier versions, you might as well use string comparisons:

SELECT 'DROP TABLE "' + TABLE_NAME + '"' AS 'Command'
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_NAME LIKE 'tableA%' OR TABLE_NAME LIKE 'tableB%') AND
      (SUBSTRING(TABLE_NAME, 7, 8), 112) < CONVERT(VARCHAR(8), @cutoffDate, 112))
ORDER BY Command DESC;

This converts the cutoff date to a string in the format of YYYYMMDD, which is fine for this comparison. However, you do need to be careful about the values that do not match the specific format -- this might accidentally delete a table that you don't intend to delete.