如何编写函数获取当前月份+6个月的全月销售记录？

Got it, let's work through this problem together. You need to fetch all sales records from Table_Date for the entire month that's 6 months after the current one—so if today falls in May 2018, you want every entry from November 1 to November 30 (or 31, depending on the month) of that same year.

The key here is to avoid hardcoding dates (since months have varying lengths) and use your database's built-in date functions to calculate the start and end of the target month automatically. Below are solutions for the most common SQL dialects:

MySQL #

Use DATE_ADD() to shift the current date forward 6 months, then DATE_FORMAT() to get the first day of that month, and LAST_DAY() to grab the last day (no more worrying about 30 vs 31 days!):

SELECT *
FROM Table_Date
WHERE your_date_column BETWEEN 
    DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 6 MONTH), '%Y-%m-01')
    AND LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 6 MONTH));

Note: Replace your_date_column with the actual name of the date column in your table.

SQL Server #

Leverage DATEADD() to add 6 months, DATEFROMPARTS() to build the first day of the target month, and EOMONTH() to get the last day effortlessly:

-- Clean, readable version with variables
DECLARE @TargetMonthStart DATE = DATEADD(MONTH, 6, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1));
DECLARE @TargetMonthEnd DATE = EOMONTH(@TargetMonthStart);

SELECT *
FROM Table_Date
WHERE your_date_column BETWEEN @TargetMonthStart AND @TargetMonthEnd;

Or a one-liner if you prefer:

SELECT *
FROM Table_Date
WHERE your_date_column >= DATEFROMPARTS(YEAR(DATEADD(MONTH, 6, GETDATE())), MONTH(DATEADD(MONTH, 6, GETDATE())), 1)
  AND your_date_column <= EOMONTH(DATEADD(MONTH, 6, GETDATE()));

PostgreSQL #

Use DATE_TRUNC() to get the start of the target month, then calculate the end by adding a month and subtracting one day:

SELECT *
FROM Table_Date
WHERE your_date_column BETWEEN 
    DATE_TRUNC('month', CURRENT_DATE + INTERVAL '6 months')::DATE
    AND (DATE_TRUNC('month', CURRENT_DATE + INTERVAL '6 months') + INTERVAL '1 month - 1 day')::DATE;

Important Notes: #

• Always use date/datetime columns instead of storing dates as strings—this avoids conversion errors and makes date operations much smoother.
• Never hardcode the end of the month (like %Y-%m-30)—months like January, March, and December have 31 days, and February has 28 or 29. Using the database's built-in end-of-month functions eliminates this bug.

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