WHERE clause operator requires compatible variables

Where can I check which data conversion I need (in this case and in others)? In SQL Server 2008R2 repo_date is a datetime column.

PNPTestovir asked May 31, 2016 at 13:43 PNPTestovir PNPTestovir 307 3 3 gold badges 5 5 silver badges 12 12 bronze badges Is repo_date datatype is datetime ? Commented May 31, 2016 at 13:51

2 Answers 2

You are comparing a string to a numeric value. So your datetime-format is wrong (like Heinzi mentioned), and also you have to convert it to a datetime value (by adding a dt at the end)

Working should this:

WHERE repo_date ='29APR2016 00:00:00.000'dt; 

If repo_time is datetime and the time is not relevant, you can just compare the date:

WHERE datepart(repo_date) = '29APR2016'd; 
answered May 31, 2016 at 14:03 1,646 1 1 gold badge 17 17 silver badges 28 28 bronze badges d makes it a date, not datetime. dt makes it datetime. Commented May 31, 2016 at 14:04

@Joe: Strange, in my sasbase d works for both (and time also). But I change it to dt to be completly correct.

Commented May 31, 2016 at 14:05

It's possible that's because the conversion to SQL Server is translating it automatically. But I would definitely not rely on that, and would use dt since that is correct.

Commented May 31, 2016 at 14:08

@Joe: You are correct, i checked my results, d converts the datetime to date automatically, so there is no error but you only have date left, with dt it works as intended.

Commented May 31, 2016 at 14:10

SAS is using the ODBC libname engine to translate SAS data step code into SQL code. Because you're writing it in SAS, SAS is assuming that you are looking for the string 2016-04-29 00:00:00.000 . Instead, you want to put it in a SAS date literal so SAS knows how to translate the data.

LIBNAME SQL ODBC DSN='sql server' ; DATA new; SET SQL.fx; WHERE repo_date = '29APR2016:00:00:00'dt; RUN; PROC PRINT DATA=new; RUN; 

If you were doing SQL passthrough to directly run SQL on the server, then your above code would work.

proc sql noprint; connect to odbc(datasrc='sql server'); create table new as select * from connection to odbc (select * from schema.fx where repo_date='2016-04-29 00:00:00.000'); disconnect from odbc; quit; 

Basically, what the above is doing is having the SQL server pull the columns, then SAS simply pulls it all over to itself. Think of it as using SAS as a proxy program to run commands directly on the SQL server.