Querying a table with a date/time filter in SQL Server 2008

I have a table in my database. This table is called Order. It has a structure like this

• ID
• CustomerID
• OrderDate
• Quantity
• Price

I need to get all of the orders for the past 2 weeks. How do I do that? I开发者_开发知识库 don't understand how to work with dates in this manner in SQL.

Thank you!

You could incorporate something like this into your WHERE clause:

WHERE OrderDate >= DATEADD(day,-14,GetDate())

(i.e OrderDate is more recent than today minus 14 days.)

[I don't have access to SQL Server here so I can't check it - but it might work :)]

Edit: Depending on the exact datatype of OrderDate, I'm not sure what will happen in cases where you have e.g. an order half way through the day two weeks ago, so you might want to check what happens.

marnir answer is the way to do it but this is another option excluding OrderDate > today:

select * from Order
where [OrderDate] 
BETWEEN DATEADD(dd, -14, GetDate()) AND GetDate()

Here is another possible way of retrieving orders placed in the last 2 weeks. This is assuming that OrderDate is a column of data type datetime. Screenshot #1 shows sample data in a table named dbo.[Order] similar to your requirements and output of below query against that table data. This query was tested in SQL Server 2008 R2 but is compatible with other SQL Server versions as well.

SELECT  Id
    ,   CustomerId
    ,   OrderDate
    ,   Quantity
    ,   Price 
FROM    dbo.[Order] 
WHERE   DATEDIFF(WEEK, OrderDate, GETDATE()) <= 2

Hope that helps.

Screenshot #1