How can I edit this Oracle code to filter for a datetime?

Question

I run the queries below in SAS; the first is to pull from Oracle data, and the second is to filter to reports after 2020. The code works but I'd like to do this all in one query. I cannot figure out how to filter the top query using SAS's datepart function. I'm guessing I need some Oracle type of equivalent for this. Can anyone help adjust the top query so it filters the same as the bottom query?

proc sql;

connect to oracle(uid="&myid." pwd="&pid_pw." path="&mypath.");
create table TESTTABLE1 as
    select * from connection to oracle
        (
    SELECT distinct
        REPORT_TABLE.ID,
        REPORT_TABLE.TIMEVALUE
    FROM POLICY.REPORT_TABLE
        );
disconnect from oracle;

quit;

PROC SQL;

CREATE TABLE WORK.TESTTABLE2 AS

SELECT t1.*

  FROM WORK.TESTTABLE1 t1
  WHERE DATEPART(t1.TIMEVALUE) ge 21915;

QUIT;

In the pass through SQL code a where criteria that uses an Oracle datetime literal. docs.oracle.com/cd/B19306_01/server.102/b14200/… — Richard

kfinity kfinity · Accepted Answer · 2021-05-27T15:13:47

I think you're looking for:

SELECT distinct
    REPORT_TABLE.ID,
    REPORT_TABLE.TIMEVALUE
FROM POLICY.REPORT_TABLE
WHERE REPORT_TABLE.TIMEVALUE >= date '2020-01-01'

(SAS date value 21915 = date '1960-01-01' + 21915 = date '2020-01-01')

How can I edit this Oracle code to filter for a datetime?

1 Answers