compare 6 month old data between 2 tables

• rui_fro

  SSC-Addicted

  Points: 467

  More actions

  June 20, 2008 at 4:15 am

  #188214

  Hi there!

  I want to compare values from two tables but only values which are 6 month old.

  So I'm using this query:

  select * from Table1 as a

  EXCEPT

  select * from Table2 as b

  where ((datepart(dd,b.datefield) <= datepart(dd,GETDATE()))and

  (datepart(mm,b.datefield) <= datepart(mm,dateadd(mm,-6,GETDATE()))) and

  (datepart(yy,b.datefield) <= datepart(yy,GETDATE())))

  The clause 'where' appears to do nothing, so it returns all values. What seems to be the

  problem?

  Thanks for any help.

• rui_fro

  SSC-Addicted

  Points: 467

  More actions

  June 20, 2008 at 4:32 am

  #831618

  Hi once again!

  Forget about the last post. I've found the solution.

  The clause 'where' needs to be on both tables, like this:

  select * from Table1 as a

  where ((datepart(dd,a.datefield) <= datepart(dd,GETDATE())) and

  (datepart(mm,a.datefield) <= datepart(mm,dateadd(mm,-6,GETDATE()))) and

  (datepart(yy,a.datefield) <= datepart(yy,GETDATE())))

  EXCEPT

  select * from Table2 as b

  where ((datepart(dd,b.datefield) <= datepart(dd,GETDATE())) and

  (datepart(mm,b.datefield) <= datepart(mm,dateadd(mm,-6,GETDATE()))) and

  (datepart(yy,b.datefield) <= datepart(yy,GETDATE())))

  😀

• bcronce

  SSCrazy

  Points: 2597

  More actions

  June 20, 2008 at 8:02 am

  #831780

  would something like this work?

  select * from Table2 as b

  where (b.datefield) <= convert(datetime, convert(varchar,getdate(),101) + ' 23:59:59.997'))

  as it wouldn't be running a function on a column and atleast gives you the option of using an index