Date comparison function in SQL Server

I am trying to display records which are created after Oct 1 2010. But my query doesn't seem to work. It also display records from 2004 - Sept 2010 which is not wanted.

What is wrong with the query below?

select Distinct app.app_id,
  (convert(varchar, creation_date,101) + ' ' +  convert(varchar,creation_date ,108)) as creation_date,
  dbo.oea_fn_get_amc_mem_name(app.app_id,primary_msn,getdate(), 'EN', 30000) PIName,
  dbo.oea_fn_get_pid_countyname(app.app_id,primary_msn,'OC')as PIpid,
  primary_msn,
  zip,
  home_tel,
  work_tel,
  work_extn,
  other_contact,
  other_ext,
  cell_tel,

  dbo.oea_fn_get_amc_mem_name(app.app_id,mem.msn,getdate(), 'EN', 30000)as Kname,
  dbo.oea_fn_get_pid_countyname(app.app_id,mem.msn,'OC')as Knamepid,
  mem.msn as Kmsn,
  (select count(reminder_id) from reminders (nolock) where app_id=app.app_id) as reminder

from app_application app  (nolock)
inner join app_member mem  with (nolock) on  app.app_id=mem.app_id 
--left outer join Oea_App_Program_Disposition disp with (nolock) on mem.app_id = disp.app_id and mem.msn=disp.msn
inner join app_address aadd with (nolock) on app.app_id=aadd.app_id
--inner join app_calc_results calc  with (nolock) on mem.app_id=calc.app_id and calc.msn=mem.msn
left outer join app_member_benefits ben with (nolock) on mem.app_id = ben.app_id and mem.msn=ben.msn

where  
 isnull(mem.coverage_required,0) = 1
 and app.app_status = 's'
     and ben.ins_end_date < getdate()
     and app.client_id = 30000
     and app.app_id  not in (selec开发者_开发问答t app_id from app_renewal) 
     and (mem.msn in (select calc.msn from app_calc_results calc 
                 inner join app_application app on calc.app_id = app.app_id and calc.prog_id = 'CK' and calc.opt_out = 1))
     and (mem.msn in (select msn from app_calc_results where app_id=app.app_id and status not in ('A','X')))
     or (mem.msn in (select msn from Oea_App_Program_Disposition where app_id=app.app_id and disp_status not in ('A','P')) )
     and app.creation_date >= '10/01/2010'

Thanks for all the help.

You probably want this:

 and (
     (mem.msn in (select calc.msn from app_calc_results calc 
                 inner join app_application app on calc.app_id = app.app_id and calc.prog_id = 'CK' and calc.opt_out = 1))
    or (mem.msn in (select msn from app_calc_results where app_id=app.app_id and status not in ('A','X')))
    or (mem.msn in (select msn from Oea_App_Program_Disposition where app_id=app.app_id and disp_status not in ('A','P')) )
     )
 and app.creation_date >= '10/01/2010'

The problem is with the logic behind the or in the where clause.

As others have stated, the problem is likely the Or clause in the Where clause. In effect, your query is:

Select ...
From ..
Where (A And B And C 
    And D And E 
    And F And G
    And app.creation_date >= '10/01/2010'
    )
    Or mem.msn In   (
                    Select msn 
                    From Oea_App_Program_Disposition 
                    Where app_id=app.app_id 
                        And disp_status not in ('A','P')
                    )

Thus, if for any row, if the Or is true, the rest of the "Ands" are ignored. I would assume that the Or is supposed to be paired with one of the And clauses.