r - Filter a SQL Table Based on a Date using RStudio tidyverse translator

Question

Im trying to filter a table on a Azure Server using RStudio as IDE.

The idea is to do this query o a dayli basis, in a range of dates that is from 6 am (yesterday) and 6am (today)and this is the code that Im using without succes.

#Extracting data from Azure Data Warehouse

  filter(FechaHoraInicio >= paste(as.character(Sys.Date()), "06:00:00", sep = " "),
         FechaHoraFin < paste(as.character(Sys.Date() + 1), "06:00:00", sep = " ")) %>% 
  collect()

After triying this, RStudio gives the following error:

Error: nanodbc/nanodbc.cpp:1617: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find either column "Sys" or the user-defined function or aggregate "Sys.Date", or the name is ambiguous. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. 'SELECT * FROM "FIDE_Session" WHERE (("FechaHoraInicio" >= CAST(Sys.Date() AS VARCHAR(MAX)) + ' ' + '06:00:00') AND ("FechaHoraFin" < CAST(Sys.Date() + 1.0 AS VARCHAR(MAX)) + ' ' + '06:00:00'))'

It is possible to achieve something like this?

question from:https://stackoverflow.com/questions/65905218/filter-a-sql-table-based-on-a-date-using-rstudio-tidyverse-translator

Answer 1

Waitting for answers