如何在MySQL中仅通过年份和月份计算指定月份的周数

Great question! When you only have the year and month (no specific day) and want to calculate how many weeks are in that month in MySQL, you can leverage built-in date functions to figure this out without needing individual dates. Let's break this down with your example: August 2017.

Core Approach #

The key idea is to:

• Generate the first and last day of the target month using only the year and month.
• Calculate the week number of these two dates.
• Compute the difference between these week numbers (adjusting for cross-year cases) and add 1 to get the total number of weeks in the month.

Example Query for August 2017 #

Here's a ready-to-run query that calculates the number of weeks in August 2017. We'll use ISO week rules (Monday as the first day of the week, week numbers 1-53) via WEEK() mode 1:

-- Define your target year and month
SET @target_year = 2017;
SET @target_month = 8;
-- Set week mode (1 = Monday start, ISO standard; adjust as needed)
SET @week_mode = 1;

-- Generate the first day of the month
SET @first_day = MAKEDATE(@target_year, 1) + INTERVAL (@target_month - 1) MONTH;
-- Generate the last day of the month
SET @last_day = LAST_DAY(@first_day);

-- Calculate the total number of weeks
SELECT
  CASE
    -- If both dates are in the same year
    WHEN YEARWEEK(@last_day, @week_mode) >= YEARWEEK(@first_day, @week_mode) THEN
      WEEK(@last_day, @week_mode) - WEEK(@first_day, @week_mode) + 1
    -- If the last day falls into the next year's week (e.g., late December)
    ELSE
      (WEEK(MAKEDATE(@target_year, IF((@target_year % 4 = 0 AND @target_year % 100 != 0) OR @target_year % 400 = 0, 366, 365)), @week_mode) - WEEK(@first_day, @week_mode) + 1) + WEEK(@last_day, @week_mode)
  END AS total_weeks_in_month;

Running this for August 2017 returns 5, which matches the actual calendar (August 2017 spans weeks 31 to 35 inclusive).

Understanding Week Modes #

MySQL's WEEK() function supports 8 different modes that define the start of the week and how week numbers are calculated. For example:

• 0: Sunday is the first day of the week; week numbers range from 0-53.
• 1: Monday is the first day of the week; week numbers range from 1-53 (ISO standard).
• 2: Sunday is the first day of the week; week numbers range from 1-53.

Adjust @week_mode to match your business's definition of a week.

Simplified Alternative (For Non-Cross-Year Cases) #

If you don't need to handle months where the last day falls into the next year (like late December), you can use this shorter query:

SET @target_year = 2017;
SET @target_month = 8;
SET @week_mode = 1;

SET @first_day = MAKEDATE(@target_year, 1) + INTERVAL (@target_month - 1) MONTH;
SET @last_day = LAST_DAY(@first_day);

SELECT WEEK(@last_day, @week_mode) - WEEK(@first_day, @week_mode) + 1 AS total_weeks_in_month;

This works perfectly for most months, including August 2017.

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