[R] RPostgreSQL Date format problem

scottyjock scott.smith2 at nhs.net
Thu Oct 4 14:44:08 CEST 2012

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';
(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
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....

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.

More information about the R-help mailing list