使用BULK INSERT导入TXT文件时日期格式转换错误求助

问题根源 #

SQL Server的BULK INSERT默认会依据当前会话的日期格式规则解析日期值。你的环境默认采用mm/dd/yyyy格式，当遇到日份大于12的dd/mm/yyyy格式日期（如13/09/2023）时，会被判定为无效日期，触发Msg 4864转换错误。

解决方案 #

方案1：临时修改会话日期格式 #

在执行BULK INSERT前，通过SET DATEFORMAT命令将当前会话的日期解析规则设置为dmy，让SQL Server按dd/mm/yyyy格式识别日期：

SET DATEFORMAT dmy;
BULK INSERT table_name 
FROM 'filedirectory.txt' 
WITH (
    FIELDTERMINATOR = '	',
    ROWTERMINATOR = '
', 
    FIRSTROW = 2
);

注意：该设置仅对当前会话生效，不会影响其他会话或全局配置。

方案2：使用格式化文件指定列解析规则 #

如果需要更灵活的控制，可以创建XML格式化文件，先将日期列以字符串形式导入，再转换为日期类型：

1. 创建格式化文件（例如date_format.xml），适配你的表结构，将第20列定义为字符类型：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="	"/>
  <!-- 根据实际表结构，定义前19列的字段规则 -->
  <FIELD ID="20" xsi:type="CharTerm" TERMINATOR="	"/>
  <!-- 根据实际表结构，定义剩余列的字段规则 -->
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="col1" xsi:type="SQLNVARCHAR"/>
  <!-- 根据实际表结构，映射前19列 -->
  <COLUMN SOURCE="20" NAME="temp_date" xsi:type="SQLNVARCHAR"/>
  <!-- 根据实际表结构，映射剩余列 -->
 </ROW>
</BCPFORMAT>

2. 导入数据到临时表，再转换日期格式插入目标表：

-- 创建临时表
CREATE TABLE #temp_import (
    -- 匹配源文件列结构，日期列设为字符串类型
    col1 NVARCHAR(50),
    -- ...其他列
    temp_date NVARCHAR(20),
    -- ...其他列
);

-- 使用格式化文件导入数据
BULK INSERT #temp_import
FROM 'filedirectory.txt'
WITH (
    FIELDTERMINATOR = '	',
    ROWTERMINATOR = '
',
    FIRSTROW = 2,
    FORMATFILE = 'date_format.xml'
);

-- 转换日期格式并插入正式表
INSERT INTO table_name (col1, /*...其他列*/, DATE)
SELECT 
    col1,
    /*...其他列*/,
    CONVERT(DATETIME, temp_date, 103) -- 103对应dd/mm/yyyy格式
FROM #temp_import;

-- 清理临时表
DROP TABLE #temp_import;

方案3：使用OPENROWSET(BULK...)直接转换日期 #

通过OPENROWSET读取文件内容，在查询过程中直接转换日期格式：

INSERT INTO table_name (col1, /*...其他列*/, DATE)
SELECT 
    col1,
    /*...其他列*/,
    CONVERT(DATETIME, temp_date, 103)
FROM OPENROWSET(
    BULK 'filedirectory.txt',
    FORMATFILE = 'date_format.xml' -- 使用方案2中的格式化文件
) AS source_data;

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