Azure Synapse中CSV的VARCHAR转DATE部分行失败求助

以下是针对你遇到的日期转换失败问题的具体排查和解决方法：

1. 排查不可见控制字符 #

VSCode和记事本无法显示所有非打印控制字符（如零宽空格、非断空格、ASCII控制字符等），可以通过SQL逐个字符检查ASCII码定位异常：

SELECT 
    DateinText,
    STRING_AGG(ASCII(SUBSTRING(DateinText, n, 1)), ', ') AS CharASCIIValues
FROM (
    SELECT 
        [main].[CarryOverDate] AS DateinText,
        n
    FROM OPENROWSET(
        BULK 'test/test123.csv',
        DATA_SOURCE = 'ds_test',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) WITH ([CarryOverDate] VARCHAR(20)) AS [main]
    CROSS JOIN (
        SELECT TOP (20) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n 
        FROM sys.all_columns
    ) AS nums
    WHERE n <= LEN(DateinText)
      AND TRY_CONVERT(DATETIME2, LTRIM(RTRIM(DateinText)), 21) IS NULL
) AS char_check
GROUP BY DateinText;

通过对比ASCII表（0-31为控制字符，160为非断空格），可以快速定位干扰转换的异常字符。

2. 批量清理常见隐藏字符 #

如果排查出非断空格（CHAR(160)）、制表符（CHAR(9)）等常见干扰字符，直接在转换前替换：

SELECT
    [main].[CarryOverDate] AS DateinText,
    TRY_CONVERT(DATETIME2, 
        LTRIM(RTRIM(
            REPLACE(REPLACE([main].[CarryOverDate], CHAR(160), ' '), CHAR(9), ' ')
        )), 
    21) AS DateinDate
FROM
OPENROWSET(
    BULK 'test/test123.csv',
    DATA_SOURCE = 'ds_test',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
) WITH (
    [CarryOverDate] VARCHAR (20)
) AS [main]

3. 验证并适配日期格式 #

格式参数21对应yyyy-MM-dd HH:mm:ss.fff，如果源数据只有日期部分（如yyyy-MM-dd）或格式有偏差，TRY_CONVERT会失败。先查看转换失败行的实际格式：

SELECT DateinText
FROM OPENROWSET(
    BULK 'test/test123.csv',
    DATA_SOURCE = 'ds_test',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
) WITH ([CarryOverDate] VARCHAR(20)) AS [main]
WHERE TRY_CONVERT(DATETIME2, LTRIM(RTRIM(DateinText)), 21) IS NULL;

如果格式不匹配，可调整格式参数，或使用更灵活的TRY_PARSE：

TRY_PARSE([main].[CarryOverDate] AS DATETIME2 USING 'en-US') AS DateinDate

4. 调整OPENROWSET摄入参数 #

直接以DATE类型摄入失败时，可尝试指定日期格式或切换解析器版本：

SELECT [CarryOverDate]
FROM OPENROWSET(
    BULK 'test/test123.csv',
    DATA_SOURCE = 'ds_test',
    FORMAT = 'CSV',
    PARSER_VERSION = '1.0',
    HEADER_ROW = TRUE,
    DATEFORMAT = 'ymd' -- 根据实际格式调整为dmy等
) WITH (
    [CarryOverDate] DATE
) AS [main];

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