Menu
WEEK() function in MySQL is used to find week number for a given date. If the date is NULL, the WEEK() function will return NULL. Otherwise, it returns the value of week which ranges between 0 to 53. The date or datetime from which we want to extract the week.
Explanation: 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 WEEK() Function The WEEK() function returns the week number for a given date (a number from 0 to 53).
Use YEARWEEK():
SELECT *
FROM your_table
WHERE YEARWEEK(`date`, 1) = YEARWEEK(CURDATE(), 1)
Use YEARWEEK. If you use WEEKOFYEAR you will get records of previous years also.
SELECT id, name, date
FROM table
WHERE YEARWEEK(date)=YEARWEEK(NOW());
For selecting records of day, week and month use this way:
function my_func($time, $your_date) {
if ($time == 'today') {
$timeSQL = ' Date($your_date)= CURDATE()';
}
if ($time == 'week') {
$timeSQL = ' YEARWEEK($your_date)= YEARWEEK(CURDATE())';
}
if ($time == 'month') {
$timeSQL = ' Year($your_date)=Year(CURDATE()) AND Month(`your_date`)= Month(CURDATE())';
}
$Sql = "SELECT * FROM your_table WHERE ".$timeSQL
return $Result = $this->db->query($Sql)->result_array();
}
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