使用OPENROWSET时FIELDQUOTE对DateTime2字段无效的问题求助

核心原因 #

D365同步到数据湖的CSV文件里，[SINKCreatedon]字段被双引号包裹，OPENROWSET默认无法自动识别带引号的字符串为datetime2类型，触发转换错误。

解决方案 #

1. 先按字符串读取，再去引号转类型 #

显式将该字段定义为字符串类型读取，用TRIM去掉前后双引号后转换为datetime2。如果存在格式异常的行，用TRY_CAST避免整个视图创建失败：

CREATE VIEW dbo.vw_account AS
SELECT
    -- 保留其他字段定义
    TRY_CAST(TRIM('"' FROM [SINKCreatedon]) AS DATETIME2) AS SINKCreatedon
    -- 其他字段
FROM OPENROWSET(
    BULK 'abfss://<容器名>@<存储账户>.dfs.core.windows.net/account文件路径/*.csv',
    FORMAT = 'CSV',
    FIRSTROW = 2,
    FIELDQUOTE = '&quot;',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
) WITH (
    -- 其他字段类型定义
    SINKCreatedon VARCHAR(30)  -- 先以字符串类型读取
    -- 其他字段
) AS [csv_data];

2. 针对特定日期格式指定转换样式 #

如果D365导出的日期是非标准格式（比如带时区的ISO8601），用TRY_CONVERT指定对应样式码：

-- 示例：处理带时区的ISO8601格式（如2024-05-20T14:30:00Z）
TRY_CONVERT(DATETIME2, TRIM('"' FROM [SINKCreatedon]), 127) AS SINKCreatedon

常用样式码参考：

• 127：ISO8601带时区格式
• 21：yyyy-MM-dd HH:mm:ss.fff 格式
• 120：yyyy-MM-dd HH:mm:ss 格式

3. 验证FIELDQUOTE配置必要性 #

如果仅[SINKCreatedon]字段被错误包裹，其他字段无嵌套逗号等需要引号转义的情况，可尝试移除FIELDQUOTE='"'配置，让OPENROWSET直接读取字段内容，但需确保其他字段不会因无引号导致分隔错误。

4. 预处理CSV文件（可选） #

若SQL层面的方法无法解决，可通过Synapse Pipeline或Azure Data Factory创建数据流，批量去除CSV中日期字段的引号后，再用OPENROWSET读取处理后的文件。

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