Select from mysql all data from within the current week

Posted on 2009-05-01
Last Modified: 2012-05-06
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,
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

    LVL 25

    Accepted Solution


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

    You might want to think about using YEARWEEK()

    LVL 17

    Expert Comment

    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    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.
    Video by: Steve
    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…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now