Learn how to a build a cloud-first strategyRegister Now

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

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?

Thanks in advance,
 Martin
0
martinprint
Asked:
martinprint
  • 2
1 Solution
 
lwadwellCommented:
Hi martinprint,

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

lwadwell
0
 
lwadwellCommented:
martinprint,

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

You might want to think about using YEARWEEK()

lwadwell
0
 
k_murli_krishnaCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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