SQL跨表导入数据遇Msg 241日期转换错误，求助解决

Let's break down why you're still hitting this error even with datetime2 as the target type, and fix it step by step:

Common Causes & Fixes #

1. Hidden Invalid Date Values in Source Data #

Even if most rows match the mm-dd-yyyy hh:mm:ss AM/PM format, there might be outliers that look valid but aren't (e.g., 02-30-2023 which doesn't exist, blank strings, or non-date text mixed in).

How to identify bad rows:
Use TRY_PARSE or TRY_CONVERT to find rows that fail conversion:

-- Check for rows that can't be converted to datetime2
SELECT your_source_date_column
FROM your_source_table
WHERE TRY_PARSE(your_source_date_column AS datetime2 USING 'en-US') IS NULL;

Once you find these rows, you can either clean them up (fix invalid dates) or exclude them temporarily during import.

2. SQL Server Language/Date Format Mismatch #

SQL Server's default language setting might be expecting a different date format (e.g., dd-mm-yyyy if the server uses a European locale). Even with datetime2, implicit conversion can fail if the server doesn't recognize mm-dd-yyyy.

Fixes:

• Set session language to English before running the import:

  SET LANGUAGE English;
  -- Now run your INSERT/SELECT statement
  INSERT INTO target_table (target_date_column)
  SELECT your_source_date_column FROM your_source_table;
  

• Explicitly specify the format using CONVERT (replace - with / to match format code 101):

  INSERT INTO target_table (target_date_column)
  SELECT CONVERT(datetime2, REPLACE(your_source_date_column, '-', '/'), 101)
  FROM your_source_table;
  

3. Implicit Conversion Issues #

Even if the target column is datetime2, SQL Server might still use implicit conversion rules that don't handle your exact format. Always use explicit conversion to avoid this.

Recommended explicit conversion methods:

• Use TRY_PARSE with US culture (perfect for mm-dd-yyyy hh:mm:ss AM/PM):

  INSERT INTO target_table (target_date_column)
  SELECT TRY_PARSE(your_source_date_column AS datetime2 USING 'en-US')
  FROM your_source_table;
  

• Use TRY_CONVERT with format handling (works if you've set the correct session language):

  INSERT INTO target_table (target_date_column)
  SELECT TRY_CONVERT(datetime2, your_source_date_column, 100)
  FROM your_source_table;
  

4. Invisible Characters in Date Strings #

Sometimes, source date columns have hidden whitespace, tabs, or control characters that break the format. For example, an extra space before AM or a non-printable character at the start/end of the string.

How to check & fix:

• Trim whitespace first, then convert:

  INSERT INTO target_table (target_date_column)
  SELECT TRY_PARSE(LTRIM(RTRIM(your_source_date_column)) AS datetime2 USING 'en-US')
  FROM your_source_table;
  

• To identify invisible characters, run this query to check ASCII values of each character:

  SELECT 
    your_source_date_column,
    ASCII(SUBSTRING(your_source_date_column, 1, 1)) AS char1_ascii,
    ASCII(SUBSTRING(your_source_date_column, 2, 1)) AS char2_ascii,
    ASCII(SUBSTRING(your_source_date_column, 3, 1)) AS char3_ascii
  FROM your_source_table
  WHERE TRY_PARSE(your_source_date_column AS datetime2 USING 'en-US') IS NULL;
  

  Printable characters for dates should be between 45 (-), 48-57 (0-9), 65-90 (A-Z), and 32 (space).

Final Checklist #

1. Run the TRY_PARSE check to find invalid rows.
2. Clean up bad data or exclude it.
3. Use explicit conversion with TRY_PARSE/TRY_CONVERT and specify the US culture.
4. Trim whitespace to eliminate invisible character issues.

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