declare @report_id int
select @report_id = 1
declare @actiondate smalldatetime
select @actiondate = '2008-01-18'


select * from inow6.dbo.in_wf_item_arch 
where queue_id in
		(SELECT [object_id] 
		from report_objects
		where report_id = @report_id
		and [type] = 'Queue')
and (convert(char(6),creation_time,12) = convert(char(6),@actiondate,12))

Msg 468, Level 16, State 9, Line 9
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

ALTER DATABASE INOW6_REPORTS COLLATE SQL_Latin1_General_CP1_CI_AS

declare @report_id int
select @report_id = 1
declare @actiondate smalldatetime
select @actiondate = '2008-01-18'


select * from inow6.dbo.in_wf_item_arch 
where queue_id in
                (SELECT [object_id] 
                from report_objects
                where report_id = @report_id
                and [type] = 'Queue')
                 DATEADD(dd, DATEDIFF(dd,0,creation_time), 0) = DATEADD(dd, DATEDIFF(dd,0,@actiondate), 0)

SELECT [object_id] 
FROM report_objects
WHERE report_id = @report_id
   AND [type] = 'Queue' COLLATE Latin1_General_CI_AS

select
    *
from
    inow6.dbo.in_wf_item_arch  
where
    queue_id in (   SELECT
                        [object_id]  
                    from
                        report_objects 
                    where
                        report_id = @report_id 
                        and [type] = 'Queue') 
    --and DATEADD(dd, DATEDIFF(dd,0,creation_time), 0) = DATEADD(dd, DATEDIFF(dd,0,@actiondate), 0) 
    and creation_time >= DATEADD(dd, DATEDIFF(dd,0,@actiondate), 0) --creation_time equal or greater than midnight of @actiondate
    and creation_time <  DATEADD(dd, DATEDIFF(dd,0,@actiondate) + 1, 0) -- and less than midnight @actiondate + 1 day