R RODBC sprintf not working

Question

I am familiar with SAS but new to R. I am trying to pull data from a Netezza datamart and have no issue to get the data by typing in the date. But when I try to use sprintf with a dynamic date, the error message shows below. Thanks for reading my post and any help from you is appreciated, thank you!

my code works when typing the date:

test<- sqlQuery(db,"
SELECT DISTINCT
B.ACCOUNT_NB,
A.CUSTOMER_NBR,
C.DATA_AS_OF_DT as MONTH_END_ENTRY_DT_LR,
C.REGION
FROM  xxxxxxx
WHERE xxxx
AND C.DATA_AS_OF_DT= '2017-11-30'
)

Same code DON'T work when using sprintf:

dt='2017-11-30'
test<- sqlQuery(db,sprintf("
SELECT DISTINCT
B.ACCOUNT_NB,
A.CUSTOMER_NBR,
C.DATA_AS_OF_DT as MONTH_END_ENTRY_DT_LR,
C.REGION
FROM  xxxxxxx
WHERE xxxx
AND C.DATA_AS_OF_DT= %s",dt)
)

error msg is

test [1] "HY000 46 ERROR: Inconsistent datatypes 'DATE' and 'INT4'"
[2] "[RODBC] ERROR: Could not SQLExecDirect '\n SELECT DISTINCT\n ............................ AND J.DATA_AS_OF_DT= 2017-11-30\n \n \n '"

Answer 1

You should enclose the SQL string into quotation marks ('%s') within the string used in sprintf:

dt='2017-11-30'
test<- sqlQuery(db,sprintf("
SELECT DISTINCT
B.ACCOUNT_NB,
A.CUSTOMER_NBR,
C.DATA_AS_OF_DT as MONTH_END_ENTRY_DT_LR,
C.REGION
FROM  xxxxxxx
WHERE xxxx
AND C.DATA_AS_OF_DT='%s'",dt)
)

Otherwise, string '2017-11-30' is interpreted as a mathematical expression 2017-11-30 which after subtractions yields 1976.