SQL中从多选财年获取合并日期范围并使用BETWEEN筛选数据

Got it, let's break this down clearly. You've selected fiscal years 2017-18 and 2018-19, and want to filter SQL records where a date column falls between 01-Jan-2017 and 31-Dec-2019 using the BETWEEN keyword. Here's how to make that happen:

Core Idea #

The BETWEEN operator in SQL selects values within a defined range (it includes both the start and end endpoints). The key is to correctly translate your selected fiscal years into the target date range, then apply the filter to your date column.

Example Queries by Database #

Date literal syntax varies a bit across databases, so here are tailored examples for common systems:

1. MySQL / MariaDB

Use standard YYYY-MM-DD date strings, or STR_TO_DATE if you need to work with the DD-Mon-YYYY format directly:

-- Using YYYY-MM-DD (most straightforward)
SELECT *
FROM your_table
WHERE your_date_column BETWEEN '2017-01-01' AND '2019-12-31';

-- Using DD-Mon-YYYY format
SELECT *
FROM your_table
WHERE your_date_column BETWEEN STR_TO_DATE('01-Jan-2017', '%d-%b-%Y') AND STR_TO_DATE('31-Dec-2019', '%d-%b-%Y');

2. SQL Server

Stick with YYYY-MM-DD or use CONVERT for DD-Mon-YYYY formatting:

-- Basic YYYY-MM-DD version
SELECT *
FROM your_table
WHERE your_date_column BETWEEN '2017-01-01' AND '2019-12-31';

-- Using DD-Mon-YYYY with CONVERT
SELECT *
FROM your_table
WHERE your_date_column BETWEEN CONVERT(DATE, '01-Jan-2017', 106) AND CONVERT(DATE, '31-Dec-2019', 106);

3. Oracle

Oracle supports DD-Mon-YYYY directly with TO_DATE, or you can use ANSI date literals:

-- Using TO_DATE for DD-Mon-YYYY
SELECT *
FROM your_table
WHERE your_date_column BETWEEN TO_DATE('01-Jan-2017', 'DD-Mon-YYYY') AND TO_DATE('31-Dec-2019', 'DD-Mon-YYYY');

-- ANSI date literal (YYYY-MM-DD)
SELECT *
FROM your_table
WHERE your_date_column BETWEEN DATE '2017-01-01' AND DATE '2019-12-31';

Important Notes #

• Inclusivity: BETWEEN captures both the start and end dates. If your date column has time components (e.g., 2019-12-31 23:59:59), using BETWEEN '2017-01-01' AND '2019-12-31' will still include these records (most databases treat date-only strings as midnight of that day).
• Dynamic Scalability: If you need to handle multiple fiscal year selections regularly, create a lookup table to map fiscal years to their date ranges. This avoids hardcoding and makes updates easier:

  -- Sample fiscal year lookup table
  CREATE TABLE fiscal_year_mapping (
      fiscal_year VARCHAR(10),
      start_date DATE,
      end_date DATE
  );
  
  INSERT INTO fiscal_year_mapping VALUES
      ('2017-18', '2017-01-01', '2017-12-31'),
      ('2018-19', '2018-01-01', '2019-12-31');
  
  -- Query to filter based on selected fiscal years
  SELECT t.*
  FROM your_table t
  JOIN fiscal_year_mapping f 
    ON t.your_date_column BETWEEN f.start_date AND f.end_date
  WHERE f.fiscal_year IN ('2017-18', '2018-19');
  

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