sas oracle日期转换,適用於Oracle查詢中SAS宏日期的語法

我想在一夜之間查詢過去一週添加到Oracle數據庫的情況，並且需要使用宏來填充日期。如果我硬編碼實際日期，我可以運行下面的查詢。我已經嘗試過兩個單引號的宏變量&sd和&ed。請指教。適用於Oracle查詢中SAS宏日期的語法

data _null_;

sd = dhms(today()-7,00,00,00);

ed = dhms(today()-1,23,59,59);

call symput("sd", put(sd, datetime20.));

call symput("ed", put(ed, datetime20.));

run;

%put &sd &ed;

proc sql;

connect to oracle (user=x password=x path=x);

create table weekly_test as

select * from connection to oracle

(select * from x.Estimates

where state_fips_code = '41'

and altered_date between

to_date('&sd','DDMONYYYY:HH24:MI:SS')

and to_date('&ed','DDMONYYYY:HH24:MI:SS'));

disconnect from oracle;

quit;

錯誤

ORACLE execute error: ORA-01858: a non-numeric character was found where a numeric was expected.

，並用雙引號

and altered_date between

to_date("&sd",'DDMONYYYY:HH24:MI:SS')

and to_date("&ed",'DDMONYYYY:HH24:MI:SS'));

此錯誤

ERROR: ORACLE prepare error: ORA-00904: " 21MAR2012:23:59:59": invalid identifier. SQL

statement: select * from X.Estimates where state_fips_code = '41' and altered_date

between to_date(" 15MAR2012:00:00:00",'DDMONYYYY:HH24:MI:SS') and to_date("

21MAR2012:23:59:59",'DDMONYYYY:HH24:MI:SS').