SQL Server中月份补前导零CASE语句执行异常求助

Got it, let's break down the issue with your query. The problem boils down to implicit data type conversion in SQL Server.

Here's exactly what's happening:

• Your THEN clause returns a string (thanks to CONVERT(VARCHAR(2), ...)), so it would correctly produce '04' for April on its own.
• But your ELSE clause returns the raw integer value from MONTH(GETDATE()) (like 10 for October).

SQL Server requires the entire CASE expression to return a single consistent data type. Since integers have higher precedence than strings, it automatically converts the string result from the THEN clause to an integer. That means '04' gets turned into the integer 4, which is why you see '4' instead of the formatted two-digit string.

Fixes to Try #

1. Make Both Branches Return Strings

Update the ELSE clause to convert the month to a string, ensuring the entire CASE expression returns consistent string values:

SELECT CASE 
         WHEN LEN(MONTH(GETDATE())) = 1 THEN RIGHT('0' + CONVERT(VARCHAR(2), MONTH(GETDATE())), 2) 
         ELSE CONVERT(VARCHAR(2), MONTH(GETDATE())) 
       END

2. Ditch the CASE Entirely (Simpler Approach)

You don't actually need the CASE check at all! Appending '0' to the month string and taking the rightmost 2 characters works perfectly for both 1-digit and 2-digit months:

• For April (4): '0' + '4' = '04', right 2 chars is '04'
• For October (10): '0' + '10' = '010', right 2 chars is '10'

Here's the simplified, cleaner query:

SELECT RIGHT('0' + CONVERT(VARCHAR(2), MONTH(GETDATE())), 2)

3. Use FORMAT (SQL Server 2012+)

If you're running SQL Server 2012 or later, the FORMAT function makes this even more intuitive with a straightforward format specifier:

SELECT FORMAT(MONTH(GETDATE()), '00')

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