SAS oracle error

[suresh123]

I tried with below  code. 

 %LET START_ORA = %SYSFUNC(sum("&start"d,0),yymmdd10.) ;
      %LET END_ORA = %SYSFUNC(sum("&end"d,0),yymmdd10.) ;

       proc sql;
       connect to oracle(path="xxx" connection =shared);
       create table sample as 
       select * from connection to oracle 
       (select * from ori.auto as a
       where datepart(a.date) between %TSLIT(&START_ORA) and datepart(a.date) %TSLIT(&END_ORA)
       );
       quit;

Error :ORACLE prepare error :ORA-00933: SQL command not properly ended. SQL statement : select * from ori.auto as a where datepart(a.date) between '2023-04-30' and datepart(a.date) '2023-04-30'

Could you please help me resolve it?

[SASKiwi]

You are using a SAS function DATEPART in SQL that needs to be Oracle-compatible. Try removing DATEPART and see how that runs.

[LinusH]

If data is a datetime column, wy don't you do the bwtween based on datetime constants instead, thus ommitting datepart functions in the where clause.

Then you could even ommit explict SQL pass through.