code

*

data* _null_;

format x y mmddyy10.;

x=date(); *** This is the current date. It can be any data you choose.

*** Remember to hard-code a date you enter '10OCT2009'd;

format y MMDDYY10.;

y=INTNX('QTR',x,*0*, 'Beg');

put 'Original date: ' x ' Beginning QTR date: ' y;

call symput ('beginQTR',put( y,MMDDYY10.));

y=INTNX('QTR',x,*0*, 'End');

put 'Original date: ' x ' Ending QTR date: ' y;

call symput ('endQTR',put( y,MMDDYY10.));

put;

*run*;

%put &BeginQTR;

%PUT &ENDQTR;

options mprint symbolgen; *

%macro* *test*;

proc sql;

Connect to ODBC (DSN=xxx uid="xxxx" pwd="xxxx" bcp=yes);

create table tt1 as select * from connection to odbc

(select * from CaseActivityByCase_CancerCoaching where OpenedDate>'&BeginQTR' );

disconnect from odbc;

quit; *

%mend*;

%*test*;

log:

* now grab the resultset from a permanent table; MPRINT(TEST): create table tt1 as select * from connection to odbc (select * from CaseActivityByCase_CancerCoaching where OpenedDate>'&BeginQTR' ); ERROR: CLI open cursor error: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting datetime from character string.

I want select * from CaseActivityByCase_CancerCoaching where OpenedDate>'&BeginQTR'

to be solved as select * from CaseActivityByCase_CancerCoaching where OpenedDate>'10/1/2009'

How can I do that?

Thanks.