Snowflake CAST(@a AS DATE)

[anthonysena]

Similar to #370, Snowflake has an issue with casting to date. Here is a reprex:

connectionDetails <- DatabaseConnector::createConnectionDetails(
  dbms = "snowflake",
  user = Sys.getenv("CDM_SNOWFLAKE_USER"),
  password = URLdecode(Sys.getenv("CDM_SNOWFLAKE_PASSWORD")),
  connectionString = Sys.getenv("CDM_SNOWFLAKE_CONNECTION_STRING")
)
cdmDatabaseSchema <- Sys.getenv("CDM_SNOWFLAKE_CDM53_SCHEMA")
vocabularyDatabaseSchema <- Sys.getenv("CDM_SNOWFLAKE_CDM53_SCHEMA")
cohortDatabaseSchema <- Sys.getenv("CDM_SNOWFLAKE_OHDSI_SCHEMA")
options(sqlRenderTempEmulationSchema = Sys.getenv("CDM_SNOWFLAKE_OHDSI_SCHEMA"))

sql <- "SELECT CAST('20231231' AS DATE);"
translatedSql <- SqlRender::translate(
  sql = sql,
  targetDialect = "snowflake"
)

connection <- DatabaseConnector::connect(connectionDetails)
#> Connecting using Snowflake driver
data <- DatabaseConnector::querySql(
  connection = connection,
  sql = translatedSql
)
print(data)
#>   CAST('20231231' AS DATE)
#> 1               1970-08-23
DatabaseConnector::disconnect(connection)

^{Created on 2024-08-08 with reprex v2.0.2}