r - handle dates and concatenate strings in dplyr (SQL) -

i have database year , doy (day of year) columns. i'd add column date, e.g.

mutate(data, date = date(julianday(year || '-01-01'), '+'||(doy-1)||' day'))  

but not work, because sql string concatenation "||" transformed "or". how that?

in case have convert string in sql date object. in r use lubridate , ymd there simple way mutate in dplyr (sql)?

christof

mutate in dplyr modifies result not modify table in database gather want.

the question did not state database being used , important in absence of sqlite used below.

1) here code using rsqlite package update table df in database date column. (doy-1) might needed in place of doy depending on origin (0 or 1) of doy. might need cast doy integer in run worked without that.

library(rsqlite)  # create test database table df con <- dbconnect(sqlite()) df <- data.frame(year = 2014, doy = 15) dbwritetable(con, "df", df)  # add date column table df , update value dbgetquery(con, "alter table df add column date") dbgetquery(con, "update df                   set date = date(cast(year integer) || '-01-01', doy || ' days')") 

giving:

dbgetquery(con, "select * df") ##   year doy       date ## 1 2014  15 2014-01-16 

2) if want modify result , not database try again assuming sqlite:

library(dplyr) library(rsqlite)  # create database , add table df db <- src_sqlite(path = tempfile(), create = true) # test database df <- data.frame(year = 2014, doy = 15) # test data dbwritetable(db$con, "df", df)  db_df <- tbl(db, "df") db_df %>%        mutate(date = sql("date(cast(year integer) || '-01-01', doy || ' days')")) 

update (1) uses rsqlite , modifies database. (2) uses dplyr , not.