This is my query for the current day:
SELECT COUNT(*) FROM blog_posts WHERE postStatus = "pending" AND DATE(date_accepted) = CURDATE()
now how about if I want to query for this week? Thanks in advance..
MySQL WEEK() Function The WEEK() function returns the week number for a given date (a number from 0 to 53).
datepart(dw, getdate()) will return the number of the day in the current week, from 1 to 7, starting with whatever you specified using SET DATEFIRST. dateadd(day, 1-datepart(dw, getdate()), getdate()) subtracts the necessary number of days to reach the beginning of the current week.
It is very easy to get current week data in MySQL. Here is the SQL query to get records of current week in MySQL. In the above query, we use now() function to get present date, and week() function to get week number of date values. So we select rows whose order_date's week number is same as week number of today's day.
MySQL CURDATE() Function The CURDATE() function returns the current date. Note: The date is returned as "YYYY-MM-DD" (string) or as YYYYMMDD (numeric). Note: This function equals the CURRENT_DATE() function.
Use the YEARWEEK()
function
WHERE YEARWEEK(date_accepted) = YEARWEEK(NOW())
Don't just use WEEK()
because that will match weeks from different years.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With