SQL：使用CASE语句拼接字符串生成带前导零的年月变量问题

Hey there, let's sort out that leading zero issue for your YEARMO field using a CASE statement exactly like you need. The problem you're seeing (getting '20182' instead of '201802') happens because the single-digit month isn't being padded with a leading zero when you concatenate. Here's how to fix it:

Example with Separate Year/Month Variables #

Let's assume you have your year and month values stored as separate VARCHAR variables (or extracted from another field). We'll use a CASE statement to check if the month is a single digit, then pad it with a zero before combining it with the year:

-- Declare your base variables (adjust these to match your actual data)
DECLARE @year VARCHAR(4) = '2018';
DECLARE @month VARCHAR(2) = '2';
DECLARE @YEARMO VARCHAR(6);

-- Use CASE to handle leading zero for single-digit months
SET @YEARMO = @year + CASE 
                        WHEN LEN(@month) = 1 THEN '0' + @month
                        ELSE @month
                      END;

-- Verify the result
SELECT @YEARMO; -- Outputs '201802'

If You're Generating from a Date Field #

If you're creating the YEARMO value directly from a date (like the current date), you can adapt the logic to pull the year and month numerically, then apply the CASE check before converting to VARCHAR:

DECLARE @currentDate DATE = GETDATE();
DECLARE @YEARMO VARCHAR(6);

SET @YEARMO = CAST(YEAR(@currentDate) AS VARCHAR(4)) + CASE 
                                                        WHEN MONTH(@currentDate) < 10 THEN '0' + CAST(MONTH(@currentDate) AS VARCHAR(1))
                                                        ELSE CAST(MONTH(@currentDate) AS VARCHAR(2))
                                                      END;

SELECT @YEARMO;

How This Logic Works #

• The CASE statement checks if the month is a single character (or less than 10 when working with numeric months).
• For single-digit months (1-9), we prepend a '0' to the month string before concatenating it to the year.
• For two-digit months (10-12), we just combine the year and month as-is.

This ensures your YEARMO field always stays in the standard 6-character YYYYMM format you need, no matter if the month is single or double-digit.

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