我想在一夜之間查詢過去一週添加到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').