mysql - 如何获得等于周、月和年/生命周期的今天的日期？

Question

我用过滤器做了报告。选项是本周、本月和终生。我的问题是它第二天也收到了。例如，今天的日期是 9 月 4 日。如果我选​​择本周，输出将是这个Sep 02 Mon, Sep 03 Tue, Sep 04 Wed, Sep 05 Thu, Sep 06 Fri, Sep 07 Sat, Sep 08 Sun

我想让这发生...

Sep 02 Mon
Sep 03 Tue
Sep 04 Wed

这个月和一生都一样...

  $sql="SELECT DATE_FORMAT(tbl_date.date, '%b %d') AS Date_Request, 
  COUNT(tbl_request_order_details.request_order_id) AS Total_Order, 
  tbl_supplier.supplier_name AS Supplier FROM (tbl_date LEFT JOIN 
  tbl_request_order ON tbl_request_order.date_request = tbl_date.date AND 
  tbl_request_order.barangay_id = $_SESSION[barangay_id]) LEFT JOIN 
  tbl_request_order_details ON tbl_request_order.request_order_id = 
  tbl_request_order_details.request_order_id LEFT JOIN tbl_supplier_medicine 
  ON tbl_supplier_medicine.supplier_medicine_id = 
  tbl_request_order_details.supplier_medicine_id LEFT JOIN tbl_supplier ON 
  tbl_supplier.supplier_id = tbl_supplier_medicine.supplier_id LEFT JOIN 
  tbl_barangay ON tbl_barangay.barangay_id = tbl_request_order.barangay_id 
  WHERE

            (";
                switch($search){
                    case 'lifetime':
                        $sql .= "(tbl_date.date between  DATE_FORMAT(NOW() 
                 ,'%M-%d-01') AND NOW())) GROUP BY tbl_date.date
                            ORDER BY tbl_date.date;";
                            break;
                    case 'this_week':
                        $sql .= "YEAR(tbl_date.date) = YEAR(NOW())
                AND WEEKOFYEAR(tbl_date.date) = WEEKOFYEAR(NOW()))
                            GROUP BY tbl_date.date
                            ORDER BY tbl_date.date;";
                            break;                  
                    case 'this_month':
                        $sql .= "YEAR(tbl_date.date) = YEAR(NOW())
                AND MONTH(tbl_date.date) = MONTH(NOW())) 
                            GROUP BY tbl_date.date
                            ORDER BY tbl_date.date;";
                            break;                 
                }
            ")";

标签： mysql