Converting date and/or time from character string causes error

Question

I have an update statement below that is auto generated when executing a proc:

BEGIN TRAN
UPDATE Core.Config
SET    Value = 'Insert',
       ModifiedBy = 'HARROGATE\Name',
       DeletedDate = '0'
WHERE  ConfigID = '1'
       AND Value = 'Update'
       AND ModifiedBy = 'HARROGATE\Name'
       AND DeletedDate = '0'
IF @@ROWCOUNT <> 1
  BEGIN;
      THROW 99999, 'Error - Single Row not updated, rollback terminated', 1;
  END;
COMMIT TRAN

-- now update the [Core].[TaskChangeLog] to confirm rollback....

UPDATE Tcl
SET    [Hasbeenrolledback] = 1,
       [Rollbackdate] = 'Jan  5 2016  9:21AM',
       tcl.Comments = Tcl.comments + ' Rollback by HARROGATE\Name'
FROM   [Core].[TaskChangeLog] Tcl
       INNER JOIN Core.TaskLog Tl
               ON Tcl.TaskLogID = Tl.TaskLogID
WHERE  Tl.TaskID = 'ABC123_New'
       AND Tcl.TaskChangeLogID = Isnull(58, Taskchangelogid)

However I receive the following error when I try to execute the update stating:

Conversion failed when converting date and/or time from character string.

I think it is the 'DeletedDate' that is causing the issue but I am not too sure. Where and why in my code is causing this error?

Code:

MERGE Core.Config AS Tgt
USING (SELECT @ConfigKey,
              @Value,
              @Deleted) AS Src (ConfigKey, Value, Deleted)
ON (Tgt.ConfigKey = Src.ConfigKey)
WHEN MATCHED THEN
  UPDATE SET Value = Src.Value,
             DeletedDate = Iif (@Deleted = 1, GETUTCDATE(), NULL),
             ModifiedBy = SUSER_SNAME(),
             ModifiedDate = GETUTCDATE()
WHEN NOT MATCHED THEN
  INSERT (ConfigKey,
          Value,
          AddedBy,
          ModifiedBy)
  VALUES (@ConfigKey,
          @Value,
          SUSER_SNAME(),
          SUSER_SNAME())
OUTPUT 'Core.Config',
       'ConfigID',
       inserted.ConfigID,
       Iif($action = 'Update', Core.Updxmlfragment('Value', inserted.Value, deleted.Value) + Core.Updxmlfragment('ModifiedBy', inserted.ModifiedBy, deleted.ModifiedBy) + Core.Updxmlfragment('DeletedDate', inserted.DeletedDate, deleted.DeletedDate ), Core.Insxmlfragment('ConfigID') + Core.Addnlxmlfragment ('ModifiedBy', inserted.ModifiedBy))

UPDATE - as asked by a user, pasted the updXMLfragment below:

IF (@InsertedValue = '')
  BEGIN
      SET @InsertedValue = ' '
  END

IF(@DeletedValue = '')
  BEGIN
      SET @DeletedValue = ' '
  END

IF(@FieldName IS NULL)
  --if(@InsertedValue IS NULL and @DeletedValue IS NULL or @FieldName IS NULL) -- pre v2.1 logic
  BEGIN
      -- This "errors" the function and ensures the script doesn't get any further than the update
      RETURN CONVERT(INT, 'NULL Params passed for XML Fragment Generation')

  END

-- Validate the date has the correct "time" element associated - i.e HH:MM:SS:MSS
IF (@FieldName LIKE '%date%')
  BEGIN

    IF((TRY_PARSE(@InsertedValue as datetime) IS NOT NULL) OR (TRY_PARSE(@DeletedValue AS datetime) IS NOT NULL))
        BEGIN
            IF((LEN(REPLACE(@DeletedValue, ':', '')) +3 <> LEN(@deletedvalue)) OR (LEN(REPLACE(@InsertedValue, ':', '')) +3 <> LEN(@InsertedValue)))
                BEGIN
                    -- This "errors" the function and ensures the script doesn't get any further than the update
                    RETURN CONVERT(DATETIME, 'Invalid Date Params passed for XML Fragment Generation')
                END
        END
  END

Answer 1

I assume that DeletedDate is DATETIME type, than update DeletedDate='0' will fail on conversion.

If you intend on updating to NULL use DeletedDate = NULL.

Also in condition you should not use DeletedDate='0'. If searching for null value use DeletedDate IS NULL.