SQL Server：如何从指定月份字符串获取上月月份字符串

Got it, let's sort this out! The problem with your original query (select CAST(Monthname-1 AS Varchar(max)) from table) is that you can't subtract 1 directly from a month name string—SQL doesn't interpret text like "June" as a numeric value, so that approach won't work. Here are two solid solutions to get the previous month's name when all you have is the month name itself:

Method 1: Use Date Conversion and DATEADD (Simplest Approach) #

We can convert the month name to a valid date (using a dummy year, since we only care about the month), subtract one month, then convert back to a month name. This automatically handles the year rollover (e.g., January → December):

SELECT 
    DATENAME(MONTH, DATEADD(MONTH, -1, CAST(Monthname + ' 01 2000' AS DATE))) AS PreviousMonthName
FROM your_table

How it works: #

1. CAST(Monthname + ' 01 2000' AS DATE): We append a day and dummy year (2000 works fine, even though it's a leap year—month conversion isn't affected) to turn the month name into a valid date (e.g., "June" becomes "2000-06-01").
2. DATEADD(MONTH, -1, ...): Subtracts one month from the date. For January, this will roll back to December of the previous year.
3. DATENAME(MONTH, ...): Converts the adjusted date back to the full month name.

Method 2: Use a Month Mapping CTE (More Flexible for Custom/Non-English Names) #

If you need to handle non-English month names or want more control over the mapping, create a common table expression (CTE) that links month numbers to names, then use it to look up the previous month:

WITH MonthList AS (
    SELECT 1 AS MonthNum, 'January' AS MonthName UNION ALL
    SELECT 2, 'February' UNION ALL
    SELECT 3, 'March' UNION ALL
    SELECT 4, 'April' UNION ALL
    SELECT 5, 'May' UNION ALL
    SELECT 6, 'June' UNION ALL
    SELECT 7, 'July' UNION ALL
    SELECT 8, 'August' UNION ALL
    SELECT 9, 'September' UNION ALL
    SELECT 10, 'October' UNION ALL
    SELECT 11, 'November' UNION ALL
    SELECT 12, 'December'
)
SELECT 
    ml2.MonthName AS PreviousMonthName
FROM your_table t
JOIN MonthList ml1 ON t.Monthname = ml1.MonthName
JOIN MonthList ml2 ON ml2.MonthNum = CASE 
                                        WHEN ml1.MonthNum = 1 THEN 12 
                                        ELSE ml1.MonthNum - 1 
                                      END

How it works: #

1. The MonthList CTE creates a reference table with each month's number and corresponding name.
2. We join your table to ml1 to get the numeric value of the current month.
3. We then join to ml2 using a CASE statement: if the current month is January (1), we map to December (12); otherwise, we subtract 1 from the month number to get the previous month's name.

Why Your Original Query Failed #

CAST(Monthname-1 AS Varchar(max)) tries to subtract 1 from a string. SQL will attempt to convert the month name to a numeric value, but since "June" isn't a number, this will either throw an error or return unexpected results. You need to work with date types or explicit mappings to make this work correctly.

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