CODEWITHSUNDEEP
mysqldate
user2881809
user2881809

Reputation:

MySQL: How to select records for this week?

I have table temp with structure on sqlfiddle:

id(int 11 primary key)
name(varchar 100)
name2(varchar 100)
date(datetime)

I would like get record on this week, example if now 21.11.2013 i would like all rows on 18.11.2013 to 24.11.2013(on week)

Now I see next algorithm:

  1. obtain weekday
  2. calculate how many days ago was Monday
  3. calculate the date Monday
  4. calculate future date Sunday
  5. make a request on date

Tell me please, is exist a shorter algorithm (preferably in the query MySQL)?

ADD Question is: Why this query select record on date 17.11.2013(Sunday) - 23.11.2013(Saturday) and how get records on date 18.11.2013(Monday) - 24.11.2013(Sunday) ?

query: 

select * from temp
where yearweek(`date`) = yearweek(curdate())

Thanks!

Upvotes: 49

Views: 76189

Answers (5)

Clary
Clary

Reputation: 636

The short solution would be this:

SELECT * FROM my_table WHERE DATE_FORMAT("2021-08-19 15:40:33", "%U") = WEEK(CURDATE());

Upvotes: 0

Ramesh
Ramesh

Reputation: 4293

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());

Upvotes: 17

dipenparmar12
dipenparmar12

Reputation: 3455

You can do it by following method

SELECT DATE_FORMAT("2017-06-15", "%U");

Get number of week (From 00 to 53 ) Where (Sunday 1st day and Monday last day of week)

May be useful for you.

example:

SELECT DISTINCT DATE_FORMAT('dates', '%U') AS weekdays FROM table_name;

Upvotes: 1

ganji
ganji

Reputation: 844

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();
}

Upvotes: 6

juergen d
juergen d

Reputation: 204746

Use YEARWEEK():

SELECT *
FROM   your_table
WHERE  YEARWEEK(`date`, 1) = YEARWEEK(CURDATE(), 1)

Upvotes: 105

Related Questions