Hi,
I'm aware of R's function to get labels from month numbers using the month function month = month(month, label = TRUE). But is there a way to do this on a database without collecting the results to be evaluated in R?
month = month(month, label = TRUE)
Note I'm looking for a SQL SERVER equivalent of DATENAME().
On second thought, I have opted to go with this solution where I wrap the SQL with sql() from dplyr.
sql()
month = sql(LEFT(DATENAME(mm,my_date),3))
I would love to know alternative solutions too.
If you are using dbplyr, then month() will translate into DATENAME for MS SQL databases: dbplyr/backend-mssql.R at 47e53ce30402d41ae4b38c803de12e63d64a9b6c · tidyverse/dbplyr · GitHub
dbplyr
month()
DATENAME
Two things I would like to achieve - (1) Get the month name (2) get the months in an ordered format. So far, month() gives me the month number and not the name. and when I use the method I suggested, I have a character vector of month names that are not ordered in the natural order.
Thanks for your suggestion, I was able to get it working with mutate(month = month(my_date, label = TRUE, abbr = FALSE)). I later did the ordering post collect().
mutate(month = month(my_date, label = TRUE, abbr = FALSE))
collect()
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed. If you have a query related to it or one of the replies, start a new topic and refer back with a link.