SELECT ProposalDate, DateDiff("wk", ProposalDate, getDate()) AS [Open Weeks]
WHERE DateDiff("wk", ProposalDate, getDate()) = 2
FROM proposals
Is it not possible to put date diff in a where clause and if so, is there a workaround? Thanks
asked
Datediff in where clause
Hi, I'm trying to run the following query, but I keep getting an error message 'Incorrect syntax near the keyword 'FROM''
You need to put the where clause last SELECT ProposalDate, DateDiff("wk", ProposalDate, getDate()) AS [Open Weeks] FROM proposals WHERE DateDiff("wk", ProposalDate, getDate()) = 2 Keep in mind that this query will be a table or index scan because you are using a function in the where clause.
Thanks for being kind enough to answer such a silly question. I'm not thinking straight today. It's one of those days.
Be aware that using a function in the where clause is a performance killer, as it prevents the query optimiser from using certain access methods. In the case you have suggested, you would be better off re-writing the where clause to something like: WHERE ProposalDate BETWEEN DATEADD(wk,-2,GETDATE()) AND GETDATE() It all depends on how fat back you really want to look, the code above will give you all proposals that fall in the period of today minus 2 weeks and today. If it were the last two calendar weeks it would need tweeking. Regardless, keep ProposalDate out of any function call - it will perform better.
