Giter Club home page Giter Club logo

Comments (11)

jimhester avatar jimhester commented on June 11, 2024

The devel version of odbc will return POSIXct objects from SQL date types as well. You can install it with devtools::install_github("rstats-db/odbc"), however you will need Rtools installed to do so.

from odbc.

bjoerm avatar bjoerm commented on June 11, 2024

Dear Jim,
Thanks for your quick response. I downloaded the development version of odbc to again test my example from above.

This lead to these results:
date_test$date is still a character. I expected a date - or given your comment a POSIXct.
date_test$casted_date is still a character. I expected a date - or given your comment a POSIXct.
date_test$casted_datetime is still a POSIXct. But now it is in YYYY-MM-DD format instead of YYYY-MM-DD hh:mm. 👍

The change regarding casted_datetime looks good to me. It is now converting SQL datetime objects of format YYYY-MM-DD into POSIXct's with YYYY-MM-DD format instead of giving them an YYYY-MM-DD hh:mm format. This is great for that case.

However, I was more referring to Mircosoft SQL date and not datetime objects. In my understanding SQL date objects (e.g. SELECT CAST('2017-01-01' AS date)) should ideally be put into either R date objects (or POSIXct's with YYYY-MM-DD format - like you are now converting YYYY-MM-DD SQL datetime objects).

I hope that I could make of what I perceive as unexpected behaviour. If something is unclear or you need further information, please just ask. :-)

Thanks a lot!

Session Info 
> devtools::session_info()
Session info -------------------------------------------------------------------------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.3.3 (2017-03-06)
 system   x86_64, mingw32             
 ui       RStudio (1.0.136)           
 language (EN)                        
 collate  German_Germany.1252         
 tz       Europe/Berlin               
 date     2017-05-24                  

Packages -----------------------------------------------------------------------------------------------------------------------------------------------------------
 package    * version    date       source                         
 assertthat   0.2.0      2017-04-11 CRAN (R 3.3.3)                 
 base       * 3.3.3      2017-03-06 local                          
 bit          1.1-12     2014-04-09 CRAN (R 3.3.0)                 
 bit64        0.9-7      2017-05-08 CRAN (R 3.3.3)                 
 blob         1.0.0      2016-12-29 CRAN (R 3.3.3)                 
 datasets   * 3.3.3      2017-03-06 local                          
 DBI        * 0.6-1      2017-04-01 CRAN (R 3.3.3)                 
 devtools     1.13.1     2017-05-13 CRAN (R 3.3.3)                 
 digest       0.6.12     2017-01-27 CRAN (R 3.3.2)                 
 dplyr      * 0.5.0      2016-06-24 CRAN (R 3.3.1)                 
 graphics   * 3.3.3      2017-03-06 local                          
 grDevices  * 3.3.3      2017-03-06 local                          
 hms          0.3        2016-11-22 CRAN (R 3.3.2)                 
 magrittr     1.5        2014-11-22 CRAN (R 3.3.2)                 
 memoise      1.1.0      2017-04-21 CRAN (R 3.3.3)                 
 methods    * 3.3.3      2017-03-06 local                          
 odbc       * 1.0.1.9000 2017-05-24 Github (rstats-db/odbc@acd17f7)
 R6           2.2.1      2017-05-10 CRAN (R 3.3.3)                 
 Rcpp         0.12.11    2017-05-22 CRAN (R 3.3.3)                 
 rlang        0.1.1      2017-05-18 CRAN (R 3.3.3)                 
 stats      * 3.3.3      2017-03-06 local                          
 tibble       1.3.1      2017-05-17 CRAN (R 3.3.3)                 
 tools        3.3.3      2017-03-06 local                          
 utils      * 3.3.3      2017-03-06 local                          
 withr        1.0.2      2016-06-20 CRAN (R 3.3.1)

from odbc.

jimhester avatar jimhester commented on June 11, 2024

This seems to be working as intended. Note the first column is a string because you have not casted the string to a date object, the AS date just names the column 'date'.

library(DBI)
con <- dbConnect(odbc::odbc(),
                 driver = "libtdsodbc.so",
                 database = "AdventureWorks2012",
                 uid = "sqlfamily",
                 pwd = "sqlf@m1ly",
                 Server = "mhknbn2kdz.database.windows.net",
                 port = 1433)

data <- dbGetQuery(con, "SELECT '2017-01-01' AS date, CAST('2017-01-01' AS date) AS casted_date, CAST('2017-01-01' AS datetime) AS casted_datetime")
str(data)
#> 'data.frame':    1 obs. of  3 variables:
#>  $ date           : chr "2017-01-01"
#>  $ casted_date    : Date, format: "2017-01-01"
#>  $ casted_datetime: POSIXct, format: "2017-01-01"

from odbc.

bjoerm avatar bjoerm commented on June 11, 2024

Dear Jim,
thanks for double-checking. You are of course right, that SELECT '2017-01-01' AS date should return a character and just casted_date be a date.

However, casted_date did for me not return a date (in opposite to your example) but a character.

I will check again once I am back in the office. Thanks a lot for already looking into this.

from odbc.

bjoerm avatar bjoerm commented on June 11, 2024

Hey Jim,

test

Your example

I cannot run your example on my computer. It results in:

library(DBI)
con <- dbConnect(odbc::odbc(),
  driver = "libtdsodbc.so",
  database = "AdventureWorks2012",
  uid = "sqlfamily",
  pwd = "sqlf@m1ly",
  Server = "mhknbn2kdz.database.windows.net",
  port = 1433)

data <- dbGetQuery(con, "SELECT '2017-01-01' AS date, CAST('2017-01-01' AS date) AS casted_date, CAST('2017-01-01' AS datetime) AS casted_datetime")
str(data)
`
Error: nanodbc/nanodbc.cpp:950: IM002: [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben
#(Translation: The data source name was not found and no default driver was provided.)

I am using Windows 10 and could not find any libtdsodbc.so driver or file named libtdsodbc on my computer.

My example

It is still like I posted in my first post:

library(odbc)
library(DBI)

con <- DBI::dbConnect(
  drv = odbc::odbc()
  , dsn = "..."
  , uid = "..."
  , pwd = "..."
  )

qry <- "SELECT '2017-01-01' AS date, CAST('2017-01-01' AS date) AS casted_date, CAST('2017-01-01' AS datetime) AS casted_datetime"

date_test <- DBI::dbGetQuery(con, qry)

str(date_test)

# Which returns:
> str(date_test)
'data.frame':	1 obs. of  3 variables:
 $ date           : chr "2017-01-01"
 $ casted_date    : chr "2017-01-01"
 $ casted_datetime: POSIXct, format: "2017-01-01"

So a character instead of a date for casted_date.

Next steps

I guess the reasons for this can be various and I understand, if you are done with this issue. However, if you still want to investigate, I can try to recreate your example if you guide me on how I connect to that server you provided.

Again thanks for the time you already invested here.

from odbc.

jimhester avatar jimhester commented on June 11, 2024

Just replace the driver line with Driver = "{SQL Server}", which will use the Windows SQL Server driver.

Also please make sure you have the latest version of odbc installed and you have restarted your R process.

from odbc.

bjoerm avatar bjoerm commented on June 11, 2024

Thanks for the help.

I sadly also don't get a date from the casted_date part. See details below. I ran it for the current Github as well as the current CRAN version.

> str(data)
'data.frame':	1 obs. of  3 variables:
$ date           : chr "2017-01-01"
$ casted_date    : chr "2017-01-01" # Still a character for me. :-(
$ casted_datetime: POSIXct, format: "2017-01-01"

Using odbc version 1.0.1.9000 (2017-06-21 Github (e5a00a6))

R Code 
library(DBI)
library(odbc)

con <- dbConnect(odbc::odbc(),
# driver = "libtdsodbc.so",
driver = "{SQL Server}",
database = "AdventureWorks2012",
uid = "sqlfamily",
pwd = "sqlf@m1ly",
Server = "mhknbn2kdz.database.windows.net",
port = 1433)

data <- dbGetQuery(con, "SELECT '2017-01-01' AS date, CAST('2017-01-01' AS date) AS casted_date, CAST('2017-01-01' AS datetime) AS casted_datetime")
str(data)

#> output
> str(data)
'data.frame':	1 obs. of  3 variables:
$ date           : chr "2017-01-01"
$ casted_date    : chr "2017-01-01"
$ casted_datetime: POSIXct, format: "2017-01-01"
Session Info 
devtools::session_info()
#> output
Session info -------------------------------------------------------------------------------------------------------------------------------------------------------
setting  value                       
version  R version 3.3.3 (2017-03-06)
system   x86_64, mingw32             
ui       RStudio (1.0.136)           
language (EN)                        
collate  German_Germany.1252         
tz       Europe/Berlin               
date     2017-06-21                  

Packages -----------------------------------------------------------------------------------------------------------------------------------------------------------
package   * version    date       source                         
base      * 3.3.3      2017-03-06 local                          
bit         1.1-12     2014-04-09 CRAN (R 3.3.0)                 
bit64       0.9-7      2017-05-08 CRAN (R 3.3.3)                 
blob        1.1.0      2017-06-21 Github (hadley/blob@9dd54d9)   
datasets  * 3.3.3      2017-03-06 local                          
DBI       * 0.7        2017-06-18 CRAN (R 3.3.3)                 
devtools    1.13.2     2017-06-02 CRAN (R 3.3.3)                 
digest      0.6.12     2017-01-27 CRAN (R 3.3.2)                 
graphics  * 3.3.3      2017-03-06 local                          
grDevices * 3.3.3      2017-03-06 local                          
hms         0.3        2016-11-22 CRAN (R 3.3.2)                 
memoise     1.1.0      2017-04-21 CRAN (R 3.3.3)                 
methods   * 3.3.3      2017-03-06 local                          
odbc      * 1.0.1.9000 2017-06-21 Github (rstats-db/odbc@e5a00a6)
Rcpp        0.12.11    2017-05-22 CRAN (R 3.3.3)                 
rlang       0.1.1      2017-05-18 CRAN (R 3.3.3)                 
stats     * 3.3.3      2017-03-06 local                          
tibble      1.3.3      2017-05-28 CRAN (R 3.3.3)                 
tools       3.3.3      2017-03-06 local                          
utils     * 3.3.3      2017-03-06 local                          
withr       1.0.2      2016-06-20 CRAN (R 3.3.1)

Using odbc version 1.0.1 (2017-02-07 CRAN (R 3.3.3))

R Code 
library(DBI)
library(odbc)
con <- dbConnect(odbc::odbc(),
# driver = "libtdsodbc.so",
driver = "{SQL Server}",
database = "AdventureWorks2012",
uid = "sqlfamily",
pwd = "sqlf@m1ly",
Server = "mhknbn2kdz.database.windows.net",
port = 1433)

data <- dbGetQuery(con, "SELECT '2017-01-01' AS date, CAST('2017-01-01' AS date) AS casted_date, CAST('2017-01-01' AS datetime) AS casted_datetime")
str(data)

#> output
> str(data)
'data.frame':	1 obs. of  3 variables:
$ date           : chr "2017-01-01"
$ casted_date    : chr "2017-01-01"
$ casted_datetime: POSIXct, format: "2017-01-01 01:00:00"
Session Info 
devtools::session_info()
#> output
Session info -------------------------------------------------------------------------------------------------------------------------------------------------------
setting  value                       
version  R version 3.3.3 (2017-03-06)
system   x86_64, mingw32             
ui       RStudio (1.0.136)           
language (EN)                        
collate  German_Germany.1252         
tz       Europe/Berlin               
date     2017-06-21                  

Packages -----------------------------------------------------------------------------------------------------------------------------------------------------------
package   * version date       source        
base      * 3.3.3   2017-03-06 local         
blob        1.1.0   2017-06-17 CRAN (R 3.3.3)
datasets  * 3.3.3   2017-03-06 local         
DBI       * 0.7     2017-06-18 CRAN (R 3.3.3)
devtools    1.13.2  2017-06-02 CRAN (R 3.3.3)
digest      0.6.12  2017-01-27 CRAN (R 3.3.2)
graphics  * 3.3.3   2017-03-06 local         
grDevices * 3.3.3   2017-03-06 local         
memoise     1.1.0   2017-04-21 CRAN (R 3.3.3)
methods   * 3.3.3   2017-03-06 local         
odbc        1.0.1   2017-02-07 CRAN (R 3.3.3)
Rcpp        0.12.11 2017-05-22 CRAN (R 3.3.3)
rlang       0.1.1   2017-05-18 CRAN (R 3.3.3)
stats     * 3.3.3   2017-03-06 local         
tibble      1.3.3   2017-05-28 CRAN (R 3.3.3)
tools       3.3.3   2017-03-06 local         
utils     * 3.3.3   2017-03-06 local         
withr       1.0.2   2016-06-20 CRAN (R 3.3.1)

Might it be related to timezone settings / the way my system handles dates (Germany)?

from odbc.

bjoerm avatar bjoerm commented on June 11, 2024

PS: Just tested it also with the new odbc version 1.1.1 (2017-06-27 CRAN (R 3.3.3)).
The casted_date is sadly still a character. :-(

Please let me know, if you need any more information or I shall try some other code.

from odbc.

shapenaji avatar shapenaji commented on June 11, 2024

We're all having the same issue internally, several different platforms,

A few OS X users getting characters others getting dates for the same query.
Windows users also can go either way depending on the machine and the installation.
Ubuntu 16.04 goes character rather than date

My details:

I get my dates as character

> sessionInfo()
R version 3.4.1 (2017-06-30)
Platform: x86_64-apple-darwin16.6.0 (64-bit)
Running under: macOS Sierra 10.12.5

Matrix products: default
BLAS: /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib
LAPACK: /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libLAPACK.dylib

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] odbc_1.1.1 curl_2.7  

loaded via a namespace (and not attached):
 [1] bit_1.1-12     compiler_3.4.1 hms_0.3        DBI_0.7        tools_3.4.1    tibble_1.3.3   Rcpp_0.12.12  
 [8] bit64_0.9-7    blob_1.1.0     rlang_0.1.1

odbcDataTypes:

odbcDataType(con, Sys.Date())
[1] "DATE"

Server details:

$dbms.name
[1] "Microsoft SQL Server"

$db.version
[1] "13.00.1601"

$drivername
[1] "libtdsodbc.so"

$odbc.version
[1] "03.52"

$driver.version
[1] "01.00.0048"

$odbcdriver.version
[1] "03.50"

> packageVersion('odbc')
[1] ‘1.1.1’

Casting as datetime DOES work, and successfully converts it to a POSIXct

from odbc.

shrektan avatar shrektan commented on June 11, 2024

For anyone who concerns, I just to share that changing driver = {SQL Server} to driver = '{SQL Server Native Client 11.0}' solves this on my computer. (Please go to control panel -> management tools -> odbc datasource, where you can find multiple drivers there. Try them one by one and maybe you can find the one that works.)

from odbc.

bjoerm avatar bjoerm commented on June 11, 2024

Thanks @shrektan,
can also report the an ODBC driver update solved this issue for me. With https://www.microsoft.com/en-us/download/details.aspx?id=56567 dates stay dates and don't become characters.

from odbc.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.