That does look like an excellent suggestion, and I can get it to work as expected in Microsoft SQL Server Management Studio:
SELECT DATEPART(hour, '2015-11-09 03:10:00')
3
SELECT DATEPART(minute,'2015-11-09 03:10:00')
10
SELECT DATEPART(second,'2015-11-09 03:10:00')
0
But I don't understand where hour, minute and second are defined?
Strings enclosed in double quotes also work, but not strings in single quotes:
SELECT DATEPART("hour", '2015-11-09 03:10:00')
SELECT DATEPART("minute",'2015-11-09 03:10:00')
SELECT DATEPART("second",'2015-11-09 03:10:00')
Either works (no quotes or double quotes) in RStudio (assuming valid TSQL connection HF):
Note: I replaced back-ticks in the code chunks below with quotes so preformatted text blocks will work.
No Quotes
' ' '{sql, connection="HF"}
SELECT DATEPART(hour, '2015-11-09 03:10:00')
' ' '
Double quotes
' ' '{sql, connection="HF"}
SELECT DATEPART("hour", '2015-11-09 03:10:00')
' ' '
Single Quotes
Error: 'SELECT DATEPART('hour', '2015-11-09 03:10:00')'
nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid parameter 1 specified for datepart.
Failed to execute SQL chunk
dplyr/SQL attempts
I can't get this to work with dplyr with or without quotes since hours always gets the single quotes in SQL that do not work:
dplyr: take4 = DATEPART(hour, LAB_COMPLETED_DT_TM)
sql: Error in UseMethod("escape") : no applicable method for 'escape' applied to an object of class "function"
dplyr: take4 = DATEPART("hour", LAB_COMPLETED_DT_TM)
sql: DATEPART('hour', "LAB_COMPLETED_DT_TM") AS "take4"
dplyr: take4 = DATEPART('hour', LAB_COMPLETED_DT_TM)
DATEPART('hour', "LAB_COMPLETED_DT_TM") AS "take4"
Is there a way to get "hour" instead of 'hour' in the SQL DATEPART call from dplyr?