SQL Select data by this week

Question

Hi how do I get the data by current week?

Select * from Transaction where transactionDate ....

Answer 1

In SQL Server based on week of year. Please see DATEPART for @@DATEFIRST etc. for example, this is all trades since Sunday in US/UK settigs:

WHERE DATEPART(week, transactionDate) = DATEPART(week, GETDATE())

Edit:

For Access, use this DatePart and use "ww" for the part of date you want. In answer to the comment, "week" is not a variable; it's the bit of the date you want

So:

WHERE DatePart("ww", transactionDate) = DatePart("ww", GETDATE())

Answer 2

In Microsoft Access

Last n days:

SELECT *
FROM Transaction 
WHERE transactionDate >=Date()-7

If you have indexes and this type of difference suits, it will be faster because it is sargable

This week by week difference:

SELECT *
FROM Transaction 
WHERE DateDiff("w",[transactionDate],Date())=0

BTW It is considered bad practice to use *

DateDiff: http://office.microsoft.com/en-us/access/ha012288111033.aspx