If you compare two character fields and you run into the collation conflict you can change the collation inside the query:

SELECT [object_id]

FROM report_objects

WHERE report_id = @report_id

AND [type] = 'Queue' COLLATE Latin1_General_CI_AS

Regarding your current scenario I second Lowells suggestion to compare date type values instead of converting date values to character.