SQL T-SQL新手求助：MS SQL Server转换特定字符串日期为mm/dd/yyyy

Hey there! I get it, dealing with messy date strings can be frustrating—especially when Excel handles it smoothly but SQL Server throws a curveball. Let's break down how to fix this.

The core issue here is that your original string includes the day of the week ("Thursday,"), which most of SQL Server's built-in conversion styles don't expect. So first, we need to strip out that extra part, then convert the remaining date components into a proper date type, and finally format it as mm/dd/yyyy.

Here are a few reliable methods to do this:

Method 1: Use TRY_CONVERT with Style 109 #

This method first extracts the part after the comma, then uses TRY_CONVERT with style 109 (which recognizes the "Month Day Year" format) to turn it into a date, before formatting it:

DECLARE @OriginalDateStr VARCHAR(50) = 'Thursday, January 02 2020';

SELECT 
  FORMAT(
    TRY_CONVERT(DATE, LTRIM(RIGHT(@OriginalDateStr, LEN(@OriginalDateStr) - CHARINDEX(',', @OriginalDateStr))), 109),
    'MM/dd/yyyy'
  ) AS FormattedDate;

• CHARINDEX(',', @OriginalDateStr) finds the position of the comma, so we can grab everything after it.
• LTRIM removes the leading space left after cutting off the day of the week.
• Style 109 tells TRY_CONVERT to expect a date in the format Mon dd yyyy (which matches our cleaned-up string).
• FORMAT then converts the date to your desired mm/dd/yyyy format.

Method 2: Use PARSE for Language-Aware Conversion #

If you prefer a more straightforward approach that natively recognizes English month names, PARSE is a great option. It's designed to handle human-readable date strings when you specify the language:

DECLARE @OriginalDateStr VARCHAR(50) = 'Thursday, January 02 2020';

SELECT 
  FORMAT(
    PARSE(LTRIM(RIGHT(@OriginalDateStr, LEN(@OriginalDateStr) - CHARINDEX(',', @OriginalDateStr))) AS DATE USING 'en-US'),
    'MM/dd/yyyy'
  ) AS FormattedDate;

• PARSE will automatically interpret the month name and day/year values correctly when using the 'en-US' locale, no need to specify a conversion style.

Method 3: Manual Component Extraction (For Edge Cases) #

If you need more control (or if the above methods don't work due to server settings), you can split the string into individual components, convert the month name to a number, then build the date manually:

DECLARE @OriginalDateStr VARCHAR(50) = 'Thursday, January 02 2020';
DECLARE @CleanedDateStr VARCHAR(50) = LTRIM(RIGHT(@OriginalDateStr, LEN(@OriginalDateStr) - CHARINDEX(',', @OriginalDateStr)));

-- Extract individual components
DECLARE @MonthName VARCHAR(20) = LEFT(@CleanedDateStr, CHARINDEX(' ', @CleanedDateStr) - 1);
DECLARE @Day VARCHAR(2) = SUBSTRING(@CleanedDateStr, CHARINDEX(' ', @CleanedDateStr) + 1, 2);
DECLARE @Year VARCHAR(4) = RIGHT(@CleanedDateStr, 4);

-- Convert month name to numeric value
DECLARE @MonthNum INT = 
  CASE @MonthName
    WHEN 'January' THEN 1
    WHEN 'February' THEN 2
    WHEN 'March' THEN 3
    WHEN 'April' THEN 4
    WHEN 'May' THEN 5
    WHEN 'June' THEN 6
    WHEN 'July' THEN 7
    WHEN 'August' THEN 8
    WHEN 'September' THEN 9
    WHEN 'October' THEN 10
    WHEN 'November' THEN 11
    WHEN 'December' THEN 12
  END;

-- Build and format the date
SELECT FORMAT(DATEFROMPARTS(@Year, @MonthNum, @Day), 'MM/dd/yyyy') AS FormattedDate;

This method is more verbose, but it gives you full control over each part of the date conversion, which can be helpful if you're dealing with inconsistent string formats.

Why Your Initial Attempts Failed #

The reason FORMAT or CONVERT didn't work directly is because those functions expect the input string to match a specific date format that doesn't include the day of the week. By stripping out the "Thursday," part first, we give SQL Server a date string it can recognize and convert properly.

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