mysql - 如何修复日期函数的 SQL 查询？

Question

我为每月状态报告编写了以下 SQL 函数。但我收到的报告是从9月30日到10月30日。我想要10月1日到10月31日的结果。必须进行哪些更改？

我使用了以下datediff函数。

SELECT DISTINCT
    d.reports_to_name AS “Manager Name:Data:190”,
    d.employee_name AS “Employee Name:Data:150”,
    d.date AS “DSR Date:Date:120”,
    (CASE 
        WHEN d.holiday = 1 THEN ‘Holiday’ 
        WHEN d.dsr_pause = 1 THEN ‘On Leave’ 
        WHEN d.dsr_created_on IS NULL THEN ‘Missed DSR’ 
        WHEN d.date = d.dsr_created_on THEN ‘On Time’ 
        ELSE ‘Delayed’ 
     END) AS ‘DSR Details:Data:120’,
    DATEDIFF(d.dsr_created_on,d.date) AS “Delayed By:Data:100”,
    d.todays_tasks AS ‘Tasks List:Text:500’
FROM
    tabCalendar DSR AS d
WHERE
    DATEDIFF(NOW(), d.date) BETWEEN 1 AND 31
ORDER BY 
    d.reports_to_name, d.employee_name, d.date ASC

我希望结果为 10 月 1 日至 10 月 31 日或任何一个月的 1 日至 31 日/30 日。

Answer 1

只需更改 where 子句，这将返回当月第一个日期到最后一个日期的数据。

WHERE d.date 
BETWEEN DATE_SUB(LAST_DAY(NOW()),INTERVAL DAY(LAST_DAY(NOW()))-1 DAY) 
AND LAST_DAY(NOW())

如果您的日期格式为“dd/mm/yyyy”，则可以使用以下查询

WHERE d.date
DATE_FORMAT(DATE_SUB(LAST_DAY(NOW()),INTERVAL DAY(LAST_DAY(NOW()))-1 DAY),"%d/%l/%Y")
AND DATE_FORMAT(LAST_DAY(NOW()), "%d/%l/%Y")