CREATE PROC SearchAllTables
@SearchStr nvarchar(100)
AS
     ....
    SET @SearchStr2 = CASE WHEN ISDATE(@SearchStr)=0
                  THEN QUOTENAME('%' + @SearchStr + '%','''')
                  ELSE @SearchStr END
     ....


    --Here's where the comparison is made. This comparison works for string and numeric types but not      datetime
     DECLARE @sql nvarchar(max)

            IF ISDATE(@SearchStr) = 0
            BEGIN
                SET @sql = 'INSERT INTO #Results SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            END
            ELSE IF ISDATE(@SearchStr) = 1
            BEGIN
                SET @sql = 'INSERT INTO #Results SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE DATEDIFF(day, CONVERT(datetime, '+ @ColumnName + ', 103), ' + @SearchStr+ ') = 0'
            END

            PRINT @sql
            EXEC sp_ExecuteSQL @sql
GO

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

declare @SearchStr datetime
set @SearchStr = convert(datetime, '2012-09-10', 103) –- dd/mm/yyyy

'WHERE  Datediff(day, CONVERT(datetime, '+ @ColumnName + ', 103), ' + @SearchStr+') = 0'

SET @SearchStr2 = CASE WHEN ISDATE(@SearchStr)=1
  THEN CONVERT(datetime,@SearchStr,103)
  ELSE QUOTENAME('%' + @SearchStr + '%','''') END