0

I want to use an R POSIXt constant in MS SQL relying as much as I can on dbplyr's automatic translation. However, it seems to get messed up:

library(dbplyr)
dat <- lubridate::ymd_hms("2022-11-12 00:01:25") 
## class(dat) 
## [1] "POSIXct" "POSIXt" 

translate_sql(!!dat, con = simulate_mssql())
## <SQL> '2022-11-12T00:01:25Z'

Thus, I need to fall back to a string representation of my date to get the literal in a format MS SQL understands:

translate_sql(!!format(dat, "%F %T"), con = simulate_mssql())
## <SQL> '2022-11-12 00:01:25'

Is this the only way to get dates formatted properly? Or is it some obscure locale setting which messes up with the R -> MS SQL translation in this case?

thothal
  • 16,690
  • 3
  • 36
  • 71
  • 1
    `POSIXt` or `POSIXct`? Have you read [Data type mappings between R and SQL Server](https://learn.microsoft.com/en-us/sql/machine-learning/r/r-libraries-and-data-types) yet? – AlwaysLearning Mar 16 '23 at 10:45
  • 1
    Also, to be `DATFORMAT`-agnostic with string-based dates/times, you would normally insert a `T` between the date and time portions, e.g.: `2022-11-12T00:01:25` – AlwaysLearning Mar 16 '23 at 10:46
  • @AlwaysLearning Thanks for pointing out that I can use a `T` between date and time, did not know that. So basically, I would need to get rid of the `Z` then? Even specifying the timezone in `GMT` keeps the `UTC` `Z`: `translate_sql(!!lubridate::ymd_hms("2022-11-12 00:01:25", tz = "GMT"), con = simulate_mssql())` – thothal Mar 16 '23 at 12:19

1 Answers1

0

After diving into the code, I identified dbplyr:::sql_escape_datetime.DBIConnection as the culprit which hardcodes a Z to the end of the datetime literal:

dbplyr:::sql_escape_datetime.DBIConnection
# function (con, x) 
# {
#     x <- strftime(x, "%Y-%m-%dT%H:%M:%OSZ", tz = "UTC")
#     sql_escape_string(con, x)
# }
# <bytecode: 0x000001edefa2e680>
# <environment: namespace:dbplyr>

I filed a bug report as at least for MS-SQL this does not yield proper datetime literals.

thothal
  • 16,690
  • 3
  • 36
  • 71