How to translate POSIXt literals to SQL datetime literals

Question

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?

Answer 1

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.