"Conversion failed when converting date and/or time from character string" even when using-CodePudding

I am building a script that rolls all the dates in a Demo system to make them appear more up to date.

The script takes the current max date in the system and marks it as the current date, then takes every other datetime record in the Database and offsets it from the current date, by the difference from the original max. For any dates exceeding 2 years in the past, it does the same, but also brings the year value within two years.

the error "Conversion failed when converting date and/or time from character string" pops up when performing an update through a cursor towards the end of the script.

The Variables being passed are:
@TableName - Finds the table that needs updated
@ColumnName - Picks the column to update and is also used to look up/Modify the columns current value
@DateMax - The current max date in the system

Update Statement:

SET @SQL_Statement_2    =   '
                            UPDATE ' @TableName '
                            SET 
                                ' @ColumnName '     =   CASE
                                    WHEN TRY_CAST(DATEADD(day, DATEDIFF(d, ' @ColumnName ', ' @Date_King_Or_Queen ')*-1, CONVERT(DATETIME, GETDATE(), 21)) AS DATETIME) >= DATEADD(year, -2, GETDATE()) THEN TRY_CAST(DATEADD(day, DATEDIFF(d, ' @ColumnName ', ' @Date_King_Or_Queen ')*-1, CONVERT(DATETIME, GETDATE(), 21)) AS DATETIME)
                                    WHEN TRY_CAST(DATEADD(day, DATEDIFF(d, ' @ColumnName ', ' @Date_King_Or_Queen ')*-1, CONVERT(DATETIME, GETDATE(), 21)) AS DATETIME) <=DATEADD(year, -2, GETDATE())  THEN  TRY_CAST(DATEADD(yy, (DATEDIFF(YEAR, DATEADD(day, DATEDIFF(d, ' @ColumnName ', ' @Date_King_Or_Queen ')*-1, GETDATE()), GETDATE())) - 2, DATEADD(day, DATEDIFF(d, ' @ColumnName ', ' @Date_King_Or_Queen ')*-1, GETDATE()))AS DATETIME)
                                    ELSE NULL END 
                            '
EXECUTE(@SQL_Statement_2)

I attempted to use TRY_CONVERT and TRY_CAST to bypass any errors, because I don' think this needs to sweat the small stuff, given its rolling through a significant number of records, but it still is causing issues.

I have tried importing these into cursor values and updating value by value, using the current value in the where statement to keep it to only the right records, instead of using the case statement in the update, which lets it run with no errors, but it doesn't actually update anything after a long run time.

Here is example output of what the case statement does: (Yes, I need to work on the new time always being the same)

TABLE_NAME  COLUMN_NAME DATA_TYPE   Current_Date_Value  New_Date    
InvoicePayment  LastUsedDate    datetime    2012-10-29 17:01:41.063 2020-11-01 11:16:07.543 
InvoicePayment  LastUsedDate    datetime    2012-11-08 14:49:27.000 2020-11-11 11:16:07.543 
InvoicePayment  LastUsedDate    datetime    2012-11-08 14:49:29.000 2020-11-11 11:16:07.543 
InvoicePayment  LastUsedDate    datetime    2012-11-08 14:49:34.000 2020-11-11 11:16:07.543 
InvoicePayment  LastUsedDate    datetime    2012-11-08 14:49:43.000 2020-11-11 11:16:07.543

CodePudding user response：

Presumably the data type of @Date_King_Or_Queen is a datetime; this is the problem. You have syntax like @Date_King_Or_Queen ')*-1'; the varchar value ')*-1' (surprisingly) isn't a valid datetime value, and hence you get the error.

The real problem is you are injecting the value of your parameter, not parametrising it. If you parameterise it, I am sure the error will go away. This is just one, of several, reasons why you should be using sys.sp_executesql not EXEC(@SomeVariable); as you can't parameterise the latter. This results in the following:

DECLARE @SQL_Statement_2 nvarchar(MAX),
        @TableName sysname = N'YourTable',
        @ColumnName sysname = N'YourColumn',
        @Date_King_Or_Queen datetime;

DECLARE @CRLF nchar(2) = NCHAR(13)   NCHAR(10);

SET @SQL_Statement_2 = N'UPDATE dbo.'   QUOTENAME(@TableName)   @CRLF  
                       N'SET '   QUOTENAME(@ColumnName)   N' = CASE WHEN DATEADD(day, DATEDIFF(d, '   QUOTENAME(@ColumnName)   N', @Date_King_Or_Queen)*-1, CONVERT(DATETIME, GETDATE(), 21)) >= DATEADD(year, -2, GETDATE()) THEN DATEADD(day, DATEDIFF(d, '   QUOTENAME(@ColumnName)   N', @Date_King_Or_Queen)*-1, CONVERT(DATETIME, GETDATE(), 21))'   @CRLF  
                       N'                             WHEN DATEADD(day, DATEDIFF(d, '   QUOTENAME(@ColumnName)   N', @Date_King_Or_Queen)*-1, CONVERT(DATETIME, GETDATE(), 21)) <= DATEADD(year, -2, GETDATE()) THEN DATEADD(yy, (DATEDIFF(YEAR, DATEADD(day, DATEDIFF(d, '   QUOTENAME(@ColumnName)   N', @Date_King_Or_Queen)*-1, GETDATE()), GETDATE())) - 2, DATEADD(day, DATEDIFF(d, '   QUOTENAME(@ColumnName)   N', @Date_King_Or_Queen)*-1, GETDATE()))'   @CRLF  
                       N'                             ELSE NULL END;';

EXEC sys.sp_executesql @SQL_Statement_2, N'@Date_King_Or_Queen datetime', @Date_King_Or_Queen;

Of cousre, this is impossible for me to test, as I don't have access to your instance, or the data types of the parameters used.