RODBC调用sqlQuery查询MySQL日期字段时出现日期格式报错

问题复现 #

在R环境中调用RODBC包连接MySQL取数时，查询不含日期字段的表可正常返回结果，加入BIRTHDATE日期字段后触发解析报错，复现代码与运行信息如下：

library(RODBC)
mycon<-odbcConnect("nicer_64")
# 不含日期字段查询正常
data1<-sqlQuery(mycon,paste("select PID, NAME, FIRSTNAME1, SEXE from a_patient;"))
head(data1)
    PID     NAME       FIRSTNAME1 SEXE
1 22145 PILLONEL   GASTON ANTONIN    1
2 22146  PORCHER  HENRIETTE MARIE    2
3 22147    PUGIN   AUGUSTE JOSEPH    1
4 21437     HEIZ MARIE ANTOINETTE    2
5 21439     KALT             OTTO    1
6  2144  BRUNNER            MARIE    2

# 加入BIRTHDATE日期字段后报错
data2<-sqlQuery(mycon,paste("select PID, NAME, FIRSTNAME1, BIRTHDATE, SEXE from a_patient;"))
Error in as.POSIXlt.character(x, tz, ...) : 
  la chaîne de caractères n'est pas dans un format standard non ambigu
head(data2)
Error in head(data2) : object 'data2' not found

报错核心为as.POSIXlt.character函数提示字符串不符合标准无歧义时间格式，导致查询结果对象创建失败。

报错原因 #

RODBC默认会自动将MySQL返回的日期、时间类型字段转换为R本地的POSIXlt时间对象，当BIRTHDATE字段存在非标准格式值、非法日期值、格式不统一的空值时，自动解析流程会直接中断，导致整个查询失败。

解决方法 #

• 方法1：SQL层面将日期字段转为纯字符串拉取，绕开RODBC自动解析，拿到数据后在R端手动处理日期转换，兼容性最好：

# 查询时用cast把BIRTHDATE转成字符串类型
data2 <- sqlQuery(mycon, "select PID, NAME, FIRSTNAME1, cast(BIRTHDATE as char) as BIRTHDATE, SEXE from a_patient;")
# 后续可根据实际日期格式用as.Date、lubridate::ymd等函数做转换，异常值可单独定位处理

• 方法2：修改RODBC配置，关闭日期解析报错中断逻辑：

# 建立连接时添加参数，同时配置odbcDateErrors选项
mycon <- odbcConnect("nicer_64", believeNRows = FALSE, rows_at_time = 1)
options(odbcDateErrors = FALSE)

配置后无法解析的日期值会自动赋值为NA，不会阻断整个查询流程，适合快速拉数场景。

• 方法3：在MySQL端排查清洗异常日期值，从源头解决格式问题：

-- 筛选出BIRTHDATE字段中所有不合法的日期记录
SELECT PID, BIRTHDATE FROM a_patient WHERE BIRTHDATE IS NOT NULL AND DATE(BIRTHDATE) IS NULL;

将筛选出的异常日期修正为标准YYYY-MM-DD格式后，即可用原查询语句正常取数。

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