Geek Answers Handbook

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

I am trying to dynamically select tables from my database based on the name of the table, which in turn is based on the creation date. For example, tables may be named "tableA20110305" or "tableB20110305", which indicates that the tables were created on March 05, 2011.

I am trying to write a query that selects all tables so named, created before a certain cut-off date (1 year ago), and combine them in the instructions of the DROP TABLE command in a table variable. The select statement is as follows.

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 see the following error:

Msg 241, 16, 1, 14 / .

... SQL, :

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

, . .

+4
4

, , , , , , tableA<DateFormat>, .

ISDATE CASE EXPRESSION, , SUBSTRING :

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
+1

. 70-461: Querying Microsoft SQL Server 2012:

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

, . propertytype (INT, a DATE ..), propertyval . propertytype "INT", propertyval INT; , .

, , . , propertytype = 'INT' false, SQL Server CAST (propertyval AS INT) > 10, . , , , .

, , . SQL Server ; , - "--" , , . , , , , true, . , table, propertytype "INT", propertyval INT, - .

, .

:

  • TRY_CAST ( SQL Server 2012)

  • , 'tableA%' OR TABLE_NAME LIKE 'tableB%', , (CONVERT(DATETIME, SUBSTRING(TABLE_NAME, 7, 8), 112) < @cutoffDate)

+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

ISDATE(SUBSTRING(TABLE_NAME, 7, 8)) = 1 where , date , date/datetime .

+1

, - , . SQL Server 2012+ 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;

:

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 clipping date to a string in the format YYYYMMDD, which is great for this comparison. However, you need to be careful about values ​​that do not match the specific format - this may accidentally delete a table that you are not going to delete.

0
source

More articles:


All Articles