[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Calculate the date of last sunday in Mysql?

Hello,
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.

Thanks,
Rick
0
richardsimnett
Asked:
richardsimnett
1 Solution
 
Walter RitzelSenior Software EngineerCommented:
sunday calculation is:
select subdate(now(), INTERVAL (weekday(now())+1) DAY);
This return the previous sunday based on a date.
0
 
richardsimnettAuthor Commented:
Perfect!
0
 
kumargsvnCommented:
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
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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