SQL Server错误格式日期转换与批量更新技术求助

Got it, let's tackle this mixed date format issue step by step. The key here is to first spot the美式 (MM/DD/YYYY 12-hour) entries, convert them to proper datetime values, then reformat them to your desired DD-M-YYYY 24-hour style.

Step 1: Verify Conversion Logic First #

Before updating any data, always run a SELECT query to confirm the corrected dates look exactly how you want:

SELECT 
    [Date] AS Original_Date,
    CASE 
        -- Target entries with slashes (your美式 format marker) that are valid dates
        WHEN CHARINDEX('/', [Date]) > 0 AND ISDATE([Date]) = 1
        THEN 
            -- Build the date without leading zeros, plus 24-hour time
            CONVERT(VARCHAR, DATEPART(DAY, CONVERT(DATETIME, [Date], 101))) + '-' +
            CONVERT(VARCHAR, DATEPART(MONTH, CONVERT(DATETIME, [Date], 101))) + '-' +
            CONVERT(VARCHAR, DATEPART(YEAR, CONVERT(DATETIME, [Date], 101))) + ' ' +
            CONVERT(VARCHAR, CONVERT(DATETIME, [Date], 101), 108)
        -- Leave already correct DD-MM-YYYY entries untouched
        ELSE [Date]
    END AS Corrected_Date
FROM [dbo].[WSLogons]

• CHARINDEX('/', [Date]) > 0: Filters for entries using slashes (the telltale sign of your美式 format)
• ISDATE([Date]) = 1: Ensures the string can be safely converted to a datetime (avoids errors)
• CONVERT(DATETIME, [Date], 101): Turns the美式 MM/DD/YYYY string (with AM/PM) into a proper datetime value
• DATEPART extracts day/month/year without leading zeros (so you get 5 instead of 05 for May)
• CONVERT(..., 108) gives you clean 24-hour time in HH:MI:SS format

Step 2: Update the Problematic Records (Once Verified) #

If the SELECT results look perfect, run this UPDATE to fix the existing bad data:

UPDATE [dbo].[WSLogons]
SET [Date] = 
    CONVERT(VARCHAR, DATEPART(DAY, CONVERT(DATETIME, [Date], 101))) + '-' +
    CONVERT(VARCHAR, DATEPART(MONTH, CONVERT(DATETIME, [Date], 101))) + '-' +
    CONVERT(VARCHAR, DATEPART(YEAR, CONVERT(DATETIME, [Date], 101))) + ' ' +
    CONVERT(VARCHAR, CONVERT(DATETIME, [Date], 101), 108)
WHERE CHARINDEX('/', [Date]) > 0 AND ISDATE([Date]) = 1

Quick Heads-Up #

• Backup First: Always take a backup of your table before running updates—better safe than sorry if something unexpected happens.
• Ambiguous Dates: If you have entries like 03/04/2018 (could be March 4 or April 3), double-check those manually. Since we're using the美式 style (101), it will interpret this as March 4—make sure that aligns with your actual data.
• One-Time Fix: Since you've already corrected the write script, this only needs to run once to clean up the legacy data.

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