CODEWITHSUNDEEP
sqlms-access
user236501
user236501

Reputation: 8648

SQL Select data by this week

Hi how do I get the data by current week?

Select * from Transaction where transactionDate ....

Upvotes: 2

Views: 9596

Answers (6)

User123342234
User123342234

Reputation: 1455

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 :|

Upvotes: 0

KevenDenen
KevenDenen

Reputation: 1726

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.

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91356

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

Upvotes: 1

gbn
gbn

Reputation: 432261

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())

Upvotes: 8

mohdajami
mohdajami

Reputation: 9680

In IBM DB2

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

Upvotes: 0

Simon Nickerson
Simon Nickerson

Reputation: 43159

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.

Upvotes: 0

Related Questions