Synapse无服务器OPENROWSET读取CSV时日期列仅支持VARCHAR而非DATE

方案1：视图存字符串，查询时转换日期类型 #

先以字符串类型创建视图，后续查询时通过转换函数将字符串转为日期类型，同时兼容格式不匹配的情况：

-- 创建视图（日期列暂存为字符串）
CREATE VIEW dbo.CSV_Data_View
AS
SELECT
    col1,
    col2,
    date_str AS raw_date
FROM
    OPENROWSET(
        BULK 'https://yourdatalake.dfs.core.windows.net/path/to/file.csv',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        FIRSTROW = 2
    ) WITH (
        col1 INT,
        col2 VARCHAR(50),
        date_str NVARCHAR(20) -- 用字符串类型接收日期列
    ) AS [result]
GO

-- 查询时转换为日期类型
SELECT
    col1,
    col2,
    TRY_CONVERT(DATETIME2, date_str, 120) AS converted_date -- 根据实际日期格式指定style参数
FROM dbo.CSV_Data_View
WHERE TRY_CONVERT(DATETIME2, date_str, 120) IS NOT NULL -- 过滤转换失败的脏数据

方案2：自定义外部文件格式，指定日期格式 #

通过创建外部文件格式明确声明CSV中的日期/时间格式，让OPENROWSET直接解析为日期类型：

-- 创建自定义文件格式（示例日期格式为yyyy-MM-dd，日期时间则用DATETIME_FORMAT）
CREATE EXTERNAL FILE FORMAT CSV_Date_Format
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2,
        DATE_FORMAT = 'yyyy-MM-dd',
        USE_TYPE_DEFAULT = FALSE
    )
);

-- 使用自定义格式创建视图，直接指定日期类型
CREATE VIEW dbo.CSV_Data_View_Date
AS
SELECT
    col1,
    col2,
    converted_date
FROM
    OPENROWSET(
        BULK 'https://yourdatalake.dfs.core.windows.net/path/to/file.csv',
        FORMAT = 'CSV_Date_Format' -- 关联自定义文件格式
    ) WITH (
        col1 INT,
        col2 VARCHAR(50),
        converted_date DATETIME2 -- 直接指定日期类型
    ) AS [result]
GO

关键注意点 #

• 必须保证CSV中的日期格式和DATE_FORMAT/DATETIME_FORMAT完全匹配，否则仍会触发转换错误
• 若存在格式错误的脏数据，优先用TRY_CONVERT/TRY_PARSE处理，避免整批查询失败
• PARSER_VERSION 2.0对日期格式的兼容性优于1.0，建议优先使用

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