CHHK CHHK - 1 year ago 139
R Question

R: Date function in sqldf giving unusual answer (wrong date format?)

I am trying to add to a date using sqldf, i know it should be simple but I can't figure out what is wrong with my date format. Using:

sqldf("select date(model_date, '+1 day') from lapse_test")

give's answers like '-4666-01-23'

The model_date's are in the date format and look like 2015-01-01

I previously made them from a character string ('12/1/2015') using

lapse_test$model_date <- as.Date(lapse_test$date1,format = "%m/%d/%Y") or
lapse_test$model_date <- as.POSIXCT(lapse_test$date1,format = "%m/%d/%Y")

I'm guessing this is the problem? Any ideas?

Answer Source

Passing a character variable to the date() function seems to work:

df <- data.frame(a=as.Date("2010-10-01"))
df$b <- as.character(df$a)

sqldf("select date(a) from df")
#       date(a)
# 1 -4672-08-24

sqldf("select date(b) from df")
#      date(b)
# 1 2010-10-01

sqldf("select date(b, '+1 day') from df")
#   date(b, '+1 day')
# 1        2010-10-02

Note that you can do (some) arithmetic on Date objects in R directly, without needing SQL:

df$a <- df$a + 1
#            a          b
# 1 2010-10-02 2010-10-01