我试图在一夜之间查询过去一周对 Oracle 数据库的添加，并且需要使用宏来填充日期。如果我对实际日期进行硬编码，我可以运行下面的查询。我在宏 vars&sd和&ed上尝试了双引号和单引号。请指教。
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 pword=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 执行错误：ORA-01858：在需要数字的地方找到了非数字字符。
和双引号
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').
2
事实上，我不认为有必要将其格式化为日期时间文字;只需构建一个正常的 ANSI 日期字符串（YYYY-MM-DD），你也可以摆脱 TO_DATE 函数调用。
例如，尝试以下两个语句：
%let SD=%str(%')%sysfunc( putn( %sysfunc(intnx(day,%sysfunc(today()) ,-7)),yymmdd10.))%str(%');
%let ED=%str(%')%sysfunc( putn( %sysfunc(intnx(day,%sysfunc(today()) ,-1)),yymmdd10.))%str(%');
这些将 SD 定义为 today（）-7，将 ED 定义为 today（）-1（使用纯宏代码而不是数据步骤）。然后，在您的查询中，引用这些不带引号的宏变量：
proc sql;
connect to oracle (user=x pword=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 &sd and &ed
);
disconnect from oracle;
quit;
0
非常感谢 Bob。我试过你发布的代码,得到了 ORA-01861:literal 不匹配格式字符串。无论如何你让我思考正确的路径。我只是添加了代码,在数据步骤中的日期周围加上单引号,它起作用了。对于任何有类似问题的人,代码如下。
data _null_;
sd = dhms(today()-7,00,00,00);
ed = dhms(today()-1,23,59,59);
call symput('sd',"'"|| trim(left(put(sd, datetime20.)))||"'");
call symput('ed', "'"||trim(left(put(ed, datetime20.)))||"'");
run;
%put &sd &ed;
proc sql;
connect to oracle (user=x pword=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;
0
这个作品…
%LET SD = %SYSFUNC(intnx(day,"&SYSDATE9"d,-7,b),date9.) ;
%LET ED = %SYSFUNC(intnx(day,"&SYSDATE9"d,-1,b),date9.) ;
%PUT &SD &ED ;
proc sql ;
connect to oracle (user=x pword=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 %BQUOTE('&SD') and %BQUOTE('&ED')
);
disconnect from oracle ;
quit ;
本站系公益性非盈利分享网址，本文来自用户投稿，不代表码文网立场，如若转载，请注明出处
评论列表（45条）