Oracle查询中 SAS宏日期的正确语法

我试图在一夜之间查询过去一周对 Oracle 数据库的添加,并且需要使用宏来填充日期。如果我对实际日期进行硬编码,我可以运行下面的查询。我在宏 vars&sd和&ed上尝试了双引号和单引号。请指教。

我试图在一夜之间查询过去一周对 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 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 执行错误: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 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 &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 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;
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 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 %BQUOTE('&SD') and %BQUOTE('&ED')
  );
  disconnect from oracle ;
quit ;

本站系公益性非盈利分享网址,本文来自用户投稿,不代表码文网立场,如若转载,请注明出处

(127)
如何使CSS内容斜体和粗体
上一篇
从电子应用程序调用.NET核心库
下一篇

相关推荐

发表评论

登录 后才能评论

评论列表(8条)