Hi, I've just started using RPostgreSQL within R to interrogate a PostgreSQL database. The problem I have is that a column containing dates shows up differently in R than in the database: here is the output direct from the psql database in Linux: db=> select pat_dob_dt from patients where pat_dob_dt like '%1980'; pat_dob_dt ------------ 04/05/1980 01/12/1980 04/05/1980 02/10/1980 05/01/1980 25/09/1980 14/12/1980 22/07/1980 24/01/1980 01/07/1980 (10 rows) and here is output from within R: R version 2.15.1 (2012-06-22) -- "Roasted Marshmallows" Copyright (C) 2012 The R Foundation for Statistical Computing ISBN 3-900051-07-0 Platform: x86_64-redhat-linux-gnu (64-bit) R is free software and comes with ABSOLUTELY NO WARRANTY. You are welcome to redistribute it under certain conditions. Type 'license()' or 'licence()' for distribution details. Natural language support but running in an English locale R is a collaborative project with many contributors. Type 'contributors()' for more information and 'citation()' on how to cite R or R packages in publications. Type 'demo()' for some demos, 'help()' for on-line help, or 'help.start()' for an HTML browser interface to help. Type 'q()' to quit R.
library(RPostgreSQL)
Loading required package: DBI
dbhost <- Sys.getenv("PGHOST")
m <- dbDriver("PostgreSQL")
con <- dbConnect(m, dbname = "db", host = dbhost)
query <- "SELECT pat_dob_dt from patients where pat_dob_dt like '%1980';">
pat_data <- dbGetQuery(con, statement = query)
pat_data
pat_dob_dt 1 4-05-19 2 1-12-19 3 4-05-19 4 2-10-19 5 5-01-19 6 25-09-19 7 14-12-19 8 22-07-19 9 24-01-19 10 1-07-19 As you can see it strips off the last two digits in the year, and converts the "/" to a "-". The as.Date function does not help as it cannot magically make the 2 digits appear.....is this a setting in RPostgreSQL? Completely stuck....google not helped. thanks in advance.... Scott -- View this message in context: http://r.789695.n4.nabble.com/RPostgreSQL-Date-format-problem-tp4644997.html Sent from the R help mailing list archive at Nabble.com.