CSV日期列为类时间格式 导入SQL Server转换失败如何解决

问题背景 #

现有CSV文件中预期为日期类型的列仅展示为类时间格式，直接指定DATE类型导入SQL Server会报错，在Excel中修改格式也无法生效，样例数据如下：

PatientId   CreatedDate
365         43:29.1
189         12:01.4
42          05:42.7
2426        33:02.6
61          05:18.7
2442        16:10.8
15          32:59.3
8           41:16.8
127         26:47.9

核心原因 #

该列实际是完整日期时间值导出时发生了格式截断，仅展示了时分秒及毫秒的部分值，缺少日期的整数标识部分，常规转换逻辑无法识别。

解决方案 #

方案1：Excel端快速修复（小文件优先） #

• 选中CreatedDate整列，右键点击「设置单元格格式」
• 选择「数字」分类下的「自定义」，输入格式代码yyyy/m/d h:mm:ss.0，确认后查看是否展示完整日期时间
• 若已显示正确日期，直接把格式改成「日期」类型，保存CSV后即可正常导入SQL Server
• 若仍显示异常，说明导出时丢失了日期的整数序列值，先确认这批数据的生成日期范围，比如所有数据都属于2024年5月1日，就新增一列输入公式=45413+B2（45413是2024年5月1日对应的Excel日期序列值，B2为CreatedDate第一个值），再把新列设置为日期格式即可。

方案2：SQL Server导入后转换（不修改原始文件） #

• 导入CSV时，先将CreatedDate列的类型设为VARCHAR(20)，导入到临时表
• 确认数据的基准日期后执行转换语句，示例如下（基准日设为2024-05-01）：

SELECT 
    PatientId,
    CAST(
        DATEADD(SECOND, 
                LEFT(CreatedDate, CHARINDEX(':', CreatedDate) - 1) * 60 + CAST(SUBSTRING(CreatedDate, CHARINDEX(':', CreatedDate) + 1, LEN(CreatedDate)) AS DECIMAL(5,1)),
                '2024-05-01'
        ) AS DATE
    ) AS CreatedDate
FROM 临时表名

• 校验转换后的日期符合业务逻辑后，即可插入到正式业务表。

方案3：Python批量处理（大文件优先） #

如果CSV体积过大无法用Excel正常打开，用pandas批量转换：

import pandas as pd

# 读取原始CSV
df = pd.read_csv("你的原始文件路径.csv")

# 自定义转换函数，基准日可根据实际业务修改
def convert_time_str(s):
    minute_part, second_part = s.split(":")
    total_seconds = int(minute_part) * 60 + float(second_part)
    return (pd.to_datetime("2024-05-01") + pd.Timedelta(seconds=total_seconds)).date()

df["CreatedDate"] = df["CreatedDate"].apply(convert_time_str)

# 导出处理后的CSV
df.to_csv("可导入的新文件.csv", index=False)

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