Solved

# Select from mysql all data from within the current week

Posted on 2009-05-01
964 Views
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
Question by:martinprint

LVL 25

Expert Comment

Hi martinprint,

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

0

LVL 25

Accepted Solution

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

LVL 17

Expert Comment

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

### Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of demâ€¦
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦