Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 966

# Select from mysql all data from within the current week

Hi Experts,
I have the following query:

SELECT sum(paid),sum(cost) FROM `accounts` WHERE week(paidwhen) = week(CURDATE()) and year(paidwhen) = year(CURDATE()) and month(paidwhen) = month(CURDATE())

As I want to select the sum of paid and cost fields from the accounts table for the current week (Sunday to Saturday). However, mysql appears to think that the start of the week is May 1st?

Can anybody give me any pointers on where I am going wrong?

Martin
0
martinprint
• 2
1 Solution

Commented:
Hi martinprint,

by doing "and month(paidwhen) = month(CURDATE())" ... you have limited it to a single month, currently May

0

Commented:
martinprint,

BTW. using year(paidwhen) = year(CURDATE()) will affect your results across end/beginning of years.

You might want to think about using YEARWEEK()

0

Commented:
WEEKOFYEAR(date)
Returns the calendar week of the date as a number in the range from 1 to 53. WEEKOFYEAR() is a compatibility function that is equivalent to WEEK(date,3).
mysql> SELECT WEEKOFYEAR('2008-02-20');
-> 8
SELECT sum(paid),sum(cost) FROM `accounts` WHERE WEEKOFYEAR(paidwhen) = WEEKOFYEAR(CURDATE());
You can also use:
SELECT sum(paid),sum(cost) FROM `accounts` WHERE WEEK(paidwhen,3) = WEEK(CURDATE(),3);

Why compare YEAR and MONTH as well, when current week is very well decided.
0

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.