Get the records during the last month or last week

Question:
I want to query the records during the last month or last week. How can I do this easily?
Answer:
For datetime and smalldatetime, 0 stands for the default value ‘1900-01-01 00:00:00.000’. So you can use the combination of DATEDIFF and DATEADD functions to get rows within a certain period of time.

The last month
SELECT * FROM Orders
WHERE OrderDate >= DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-1,0)
AND OrderDate < DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)

The last week
SELECT * FROM Orders
WHERE OrderDate >= DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE())-1,0)
AND OrderDate < DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),0)

The year before last
SELECT * FROM Orders
WHERE OrderDate >= DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE())-2,0)
AND OrderDate < DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE())-1,0)

Here you can replace GETDATE() with any other datetime values.