> In MSSQL there is no such thing as getdate()-7. > > Why not just trim the idea a bit? > > SELECT distinct iUserID > FROM OrderHistory > WHERE tDate between dateadd(day, -7, getdate()) and > getdate()
OK. I can't understand why, but using dateadd() or using the T-SQL with variables as Ricardo recommended causes the statement to execute sub-second compared to the 17-30 second response otherwise. MSSQL Query Analyzer shows a different execution plan using getdate()-7 versus dateadd(day, -7, getdate()): http://tech.microworks.com/files/mssql.gif I'm guessing this is something to do with the MSSQL parser being inconsistent and re-executing getdate()-7 for every row in my statement and not the others... I would think "getdate()-7" should internally be converted to the exact equivelent of "dateadd(day, -7, getdate())", but apparently not... Anyways, thanks for everyone's help. -- Derek _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
