SQL技术问询：如何筛选表中审核日期为下月任意时段的数据？

Got it, let's fix that query for you! The issue with your original statement is that ADD_MONTHS(Current_Date, 1) returns exactly the same day next month (e.g., if today is 2024-05-20, it gives 2024-06-20), so using = only matches that single day instead of the entire month.

To get all records where review_date falls anywhere in the next month, you need to target the full date range of the upcoming month—from its first day up to (but not including) the first day of the month after that. Here's how to do this across common SQL dialects:

Oracle #

SELECT id, review_date 
FROM Table
WHERE CAST(review_date AS DATE) >= TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM')
  AND CAST(review_date AS DATE) < TRUNC(ADD_MONTHS(SYSDATE, 2), 'MM');

• TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM') gets the first day of next month (e.g., 2024-06-01 if today is in May)
• TRUNC(ADD_MONTHS(SYSDATE, 2), 'MM') gets the first day of the month after next (e.g., 2024-07-01)
• Using >= and < ensures we include all times on the last day of next month (like 2024-06-30 23:59:59)

MySQL #

Option 1 (using date ranges, most reliable):

SELECT id, review_date 
FROM Table
WHERE DATE(review_date) >= DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
  AND DATE(review_date) < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 2 MONTH), '%Y-%m-01');

Option 2 (using LAST_DAY):

SELECT id, review_date 
FROM Table
WHERE DATE(review_date) BETWEEN DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                            AND LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));

Note: If review_date includes time values (e.g., 2024-06-30 23:45:00), LAST_DAY will only match up to 2024-06-30 00:00:00 unless you add a time component (like LAST_DAY(...) + INTERVAL '23:59:59' HOUR_SECOND). The first option avoids this issue entirely.

PostgreSQL #

SELECT id, review_date 
FROM Table
WHERE review_date::DATE >= DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 month')::DATE
  AND review_date::DATE < DATE_TRUNC('month', CURRENT_DATE + INTERVAL '2 months')::DATE;

• DATE_TRUNC('month', ...) rounds down to the first day of the specified month
• Casting to DATE ensures we ignore any time components in review_date (adjust if you need to include time filters)

This approach works regardless of how many days are in the next month (28, 29, 30, or 31) and will reliably return all records from the entire upcoming month.

内容的提问来源于stack exchange，提问作者Gamecock1021