如何用SQL计算两个日期与指定年月的重叠天数？

Got it, let's break down how to solve this problem—finding the number of overlapping days between a given date range and a specific year-month pair. Here's a straightforward, reliable approach:

Core Logic #

The key is to find the intersection between two date ranges:

1. Your target month (e.g., January 2020: 2020-01-01 to 2020-01-31)
2. The input date range (StartDate to ENDDate)

Once you have the intersection, calculating the days is simple. Here's the step-by-step breakdown:

• First, determine the first and last day of the target year-month.
• Find the start of the overlap: the later of StartDate and the target month's first day.
• Find the end of the overlap: the earlier of ENDDate and the target month's last day.
• If the overlap start is later than the overlap end, there's no overlap (return 0). Otherwise, compute the difference between the two dates and add 1 (to include both start and end days).

Code Example (Python) #

This function handles all edge cases—no overlap, partial overlap, or full overlap of the target month:

from datetime import datetime, timedelta

def calculate_overlap_days(target_year, target_month, start_date_str, end_date_str):
    # Parse input date strings into date objects
    start_date = datetime.strptime(start_date_str, '%Y-%m-%d').date()
    end_date = datetime.strptime(end_date_str, '%Y-%m-%d').date()
    
    # Define the first day of the target month
    target_start = datetime(target_year, target_month, 1).date()
    
    # Calculate the last day of the target month (next month's first day minus 1 day)
    if target_month == 12:
        target_end = datetime(target_year + 1, 1, 1).date() - timedelta(days=1)
    else:
        target_end = datetime(target_year, target_month + 1, 1).date() - timedelta(days=1)
    
    # Find the overlapping date range
    overlap_start = max(start_date, target_start)
    overlap_end = min(end_date, target_end)
    
    # Calculate overlapping days (if any)
    if overlap_start > overlap_end:
        return 0
    return (overlap_end - overlap_start).days + 1

Test Your Examples #

Let's verify with the cases you provided:

1. Example 1: Target January 2020, date range 2019-11-12 to 2020-1-13

   print(calculate_overlap_days(2020, 1, '2019-11-12', '2020-1-13'))  # Output: 13
   

   The overlap runs from 2020-01-01 to 2020-01-13—that's 13 days total.

2. Example 2: Target September 2019, date range 2019-8-13 to 2020-1-1

   print(calculate_overlap_days(2019, 9, '2019-8-13', '2020-1-1'))  # Output: 30
   

   The entire September 2019 (30 days) falls within the input range, so the result is 30.

Notes #

• Make sure your input date strings follow the YYYY-MM-DD format. If you use a different format, adjust the strptime format code (e.g., '%d/%m/%Y' for day/month/year).
• This method works for all valid date ranges, including when the input range is entirely inside the target month, entirely outside, or only partially overlapping.

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