question

tabularyee avatar image
tabularyee 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''
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
sql-server-2005querydate-functions
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tabularyee avatar image tabularyee commented ·
Thanks for being kind enough to answer such a silly question. I'm not thinking straight today. It's one of those days.
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
Sometimes the answer is to simple and easy to miss. :)
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tabularyee avatar image tabularyee commented ·
Thank you for the additional input!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.