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

Simple but portable:

SELECT *
  FROM Transaction
    WHERE transactionDate >= ?
      AND transactionDate <= ?

Calculate the two parameters in your server-side code to whatever definition of 'week' you need.

Answer 3

In IBM DB2

SELECT *
FROM Transaction
WHERE transactionDate BETWEEN CURRENT TIMESTAMP - 7 days AND CURRENT TIMESTAMP;

Answer 4

mySQL (standard date stamp)

 SELECT *

FROM Transaction WHERE WEEK(NOW())=WEEK(transactionDate);

mySQL (unix timestamp stamp)

SELECT *

FROM Transaction WHERE WEEK(NOW())=WEEK(FROM_UNIXTIME(transactionDate));

Bit of a unoptimized query. Could be a more efficient way.

Note: This isn't a rolling 7 days. Just the current week of the year.

EDIT: Sorry I didn't see the ms-access tag. ignore all of this :|

Answer 5

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

Answer 6

In Access, if you want to run a query to find records that fall in the current week, use

SELECT *
FROM table
WHERE table.DateField Between (Date()-Weekday(Date())+1) And (Date()-Weekday(Date())+7);

That runs Sunday through Saturday. Use +2 and +6 instead if you want the workweek.