Calculate the date of last sunday in Mysql?

Posted on 2011-04-28
Last Modified: 2015-07-22
So Im constructing some queries to generate a weekly leaderboard. I need to be able to select all the records within the current week starting with the previous sunday. The logic is this...

if today is sunday, then just return todays date, if its not sunday return the date of previous sunday

that way I can query like this:

select * from quizhistory where (**SUNDAY CALCULATION** <= startdate) and (startdate < DATE_ADD(**SUNDAY CALCULATION**, INTERVAL 1 WEEK)

I have been trying to figure out how to do this using mysql date time functions but have had no luck thus far.

Working solution is worth 500 points.

Question by:richardsimnett
    LVL 15

    Accepted Solution

    sunday calculation is:
    select subdate(now(), INTERVAL (weekday(now())+1) DAY);
    This return the previous sunday based on a date.

    Author Closing Comment


    Expert Comment

    Hi Walter,

    The above query which you mentioned will work for below case also right?

    if today is sunday, then just return todays date, if its not sunday return the date of previous sunday

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    This video discusses moving either the default database or any database to a new volume.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    755 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

    24 Experts available now in Live!

    Get 1:1 Help Now