SAS qtr() function with ODBC to sql server

[Anotherdream]

Hello everyone. I have an odbc connection set up to a sql server database (example shown below). I need to pull in data from this table specified on a specific Quarter.

libname green odbc dsn='odbc_mydsn' schema=dbo bulkload=yes;

Normally I would do.

data answer;

set Green.MYtable;

where qtr(datepart(DATEFIELD))=4;

run;

The issue Is I get the error:

ERROR: A database error occurred. The database specific error follows:

       DATABASE error: The function QTRis not supported by the database ODBC

I can specify this using an IF statement, the issue is the table is VERY large, and the difference between an If statement and a where statement is far too large.

Example, a simple "where column=X" if statement runs for 3 minutes, while in a where clause it runs in 2 seconds (the column is indexed).

Does anyone know a way to pass the qtr function into a sql server database, or do I have to settle using an If statement? I am thinking of using an odbc passthrough query and passing macro variables into it.

Please note the field I am trying to pull from is a DATETIME, and I have already tried "SQL_FUNCTIONS=ALL", with the qtf(datefield)=4 and it does not work.

If anyone has any suggestions all are welcome. Thank you!

Brandon

[Anotherdream]

Please cancel this post. I am just going to do it with a proc sql step instead of a datastep...

I would like to know why the proc sql step works and the data step does not, even through they both use the odbc connection string. If anyone can explain this to me I will credit it with correct answer. AKA why does this work.....

libname green odbc dsn='odbc_mydsn' schema=dbo bulkload=yes;

proc sql;

create table testybaby as

select *

from Green.Loanlevelpopulation

where year(datepart(datamonth))=2013

and qtr(datepart(datamonth))=4;

run;

But this doesn't.

data testybaby;

set green.Loanlevelpopulation;

where year(datepart(datamonth))=2013

and qtr(datepart(datamonth))=4;

run;

Thanks all!