SQL Server中如何为YYYYMM格式日期添加月份？存储过程求助

Hey Neha, I totally get this frustration—working with YYYYMM formatted dates directly as numbers leads to messy issues when you need to cross year boundaries. Let’s break down how to solve this correctly using date functions instead of raw number arithmetic.

The Core Problem #

When you treat 202011 as a number and add 2, you get 202013 which is invalid because months only go up to 12. Instead, we need to:

1. Convert the YYYYMM string/number into a proper date object that understands month-year relationships.
2. Add the desired number of months using a date-aware function.
3. Convert the resulting date back into the YYYYMM format.

Solution by Database System #

Below are examples for the most common databases—pick the one that matches your environment:

MySQL/MariaDB

SELECT
    A,
    B,
    DATE_FORMAT(
        DATE_ADD(STR_TO_DATE(B, '%Y%m'), INTERVAL A MONTH),
        '%Y%m'
    ) AS Output
FROM your_table_name;

• STR_TO_DATE(B, '%Y%m'): Converts your YYYYMM value into a date (e.g., 202011 becomes 2020-11-01).
• DATE_ADD(..., INTERVAL A MONTH): Adds the specified number of months, automatically handling year rollover.
• DATE_FORMAT(..., '%Y%m'): Converts the updated date back to the YYYYMM string format.

SQL Server

SELECT
    A,
    B,
    FORMAT(
        DATEADD(MONTH, A, CONVERT(DATE, CONCAT(B, '01'))),
        'yyyyMM'
    ) AS Output
FROM your_table_name;

• CONCAT(B, '01'): Turns YYYYMM into YYYYMMDD (e.g., 202011 becomes 20201101), which SQL Server can convert to a date.
• DATEADD(MONTH, A, ...): Adds the months, handling year transitions correctly.
• FORMAT(..., 'yyyyMM'): Converts the result back to YYYYMM.

PostgreSQL

SELECT
    A,
    B,
    TO_CHAR(
        TO_DATE(B::TEXT, 'YYYYMM') + (A || ' months')::INTERVAL,
        'YYYYMM'
    ) AS Output
FROM your_table_name;

• TO_DATE(B::TEXT, 'YYYYMM'): Converts YYYYMM to a date.
• + (A || ' months')::INTERVAL: Adds the specified number of months.
• TO_CHAR(..., 'YYYYMM'): Formats the result back to YYYYMM.

Testing Your Sample Data #

Using any of these methods on your sample input:

You’ll get exactly the correct output you need—no more invalid 202014-style values!

The key takeaway is always to work with proper date types when manipulating dates—they’re designed to handle edge cases like month rollover automatically.

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