You don't state what issue happens when you add the WHERE.  [You don't need the N' and shouldn't use it.]

Perhaps there's some other character in the column besides...

We could give a more specific answer if you'd show the actual code.
But, for example, if it's something like this:

WHERE ISNULL(date_column, '19000101') >= '20170101'
then you don't...

INSERT INTO dbo.Tbl3 ( ID1, ID2 )
SELECT (SELECT ID1 FROM dbo.Tbl1 WHERE Name1 = @Name1),
    (SELECT ID2 FROM dbo.Tbl2 WHERE Name2 = @Name2)

SELECT Total_Input, Total_OK, Total_Fail,
     CASE WHEN Total_Fail > 0 AND Total_Fail > Total_OK THEN Total_OK
     WHEN Total_Fail > 0 THEN Total_Fail
   ...

SELECT b.time * 1.0 credit, 0.0 credits, b.Id, b.date
FROM staff b
INNER JOIN (
    SELECT *, isnull(dbo.ConvDate2(e.Startdate),'20111101') AS Startdate_converted
    FROM emp

I would hope that SQL itself would change it to an INNER JOIN when applicable, but it certainly won't hurt to try -- or verify using the query plan that...

Check to see if the db A on each instance allows cross-database ownership chaining:

SELECT is_db_chaining_on, *
FROM sys.databases
WHERE name IN ('A')

If it's 1, then security...

We would have to see the tables definition and the query itself to give you a reliable answer here.

Yes, eventually it will get compressed automatically.  Although SQL doesn't actually do any page compression until the page gets full.

Just plain DATEDIFF(DAY, ...) will do that.  By definition, DATEDIFF counts the number of times the midnight boundary is reached/crossed between two dates/datetimes.

DECLARE @CurrentYearStart INT
DECLARE @CurrentYearEnd INT
SET @CurrentYearStart = 201801
SET @CurrentYearEnd = 201805

SELECT *
FROM @test-2 t_curr
INNER JOIN @test-2 t_prev
...

To fix the immediate problem, try running:

DBCC CHECKIDENT ('dbo.CmEvent', RESEED, 55845910)

Better yet, rid yourself of the foolish and damaging myth that all tables should be clustered...