how to subtract days from date variable in sql query?

Hi,

I've searched quite a bit for help with this syntax but have given up.

I need help with the where clause of a query using SQL SERVER that selects records a certain number of days before the current date. I have tried this and it's incorrect syntax:

WHERE (fldDate < ({ fn NOW() - 500 })

Can someone please help me out with correct syntax for this? thanks much.

Check out the DATEADD and DATEDIFF T-SQL functions. You could write something like this:

WHERE DATEADD(day, -500, GETDATE())

This is untested, but is the basic idea.

Don

|||This will work in a sql server stored procedure right? (I never learned what exactly T-SQL is, i know it's transact sql, but does that mean it will work in sql server enterprise manager for example?)|||

Hi,

These are the functions provided by SQL Server. It will surely work on SQL Server stored procedure.

How to stop the TOP clause being added to query when defining a view

Hi

I've just switched over to SQL2005 Server Management Studio although most of my databases are SQL2000. Apart from noticing that everything takes longer than with Enterprise Manager I have one specific problem.

When I save a view that I have created in the visual toolbox it gets saved with the TOP 100 Percent clause added, even if I take the clause out of the SQL statement it gets put right back in when I save it. This is causing an application which worked fine before to fail with the error "cannot update a view with TOP clause"

Anyone any ideas how I can stop this clause being added?

Many thanks

? Hi Ian, >>Anyone any ideas how I can stop this clause being added? Take out the ORDER BY clause as well. An ORDER BY in a view is only allowed if there is also a TOP clause. Also, the only guaranteed effect the ORDER BY in the view will have is to define what rows to select and what rows to discard for satisfying the TOP requirement. No guarantee is made wrt the order of rows returned, unless you add an ORDER BY to the final SELECT statement you use to query your data. SQL Server 2000 did, usually, return the rows in the order that was specified in the view. New optimizing techniques in SQL Server 2005 have changed that behaviour. -- Hugo Kornelis, SQL Server MVP