Oracle Dates to SAS in Passthrough SQL

Question

I'm using SAS to connect to an Oracle database, and running into trouble with the dates. When using pass-through queries, is there a PL/SQL alternative to "DATEPART()" that will save dates as a SAS Date, rather than a SAS Timestamp? I'd prefer to avoid using LIBNAME or SAS's SQL converter, and would like to do this without an extra SAS data step.

I've experimented with DBSASTYPE= , but it doesn't seem applicable to passthrough SQL.

Example -- "SYS_DATE" from this query is stored as a SAS Timestamp rather than a SAS Date.

Proc SQL; 
Create Table WORK.DATE_ORACLE as 
Select * From Connection to ORACLE 
(
SELECT trunc(SYSDATE) as SYS_DATE FROM DUAL
); 
quit ;

You can modify the select * portion, can't you? select datepart(sys_date) from connection to ORacle..... — Reeza
Would cast(SYSDATE as date) as SYS_DATE work? (It works for SQL server, I don't have Oracle to test against). — david25272
Casting as date doesn't work -- it's returned as an Oracle date, which includes a time portion. SAS reads that as a timestamp — Netbrian

Joe Joe · Accepted Answer · 2017-10-24T01:02:22

As Reeza mentions in comments, this is probably easiest done in the non-passthrough section.

Proc SQL; 
Create Table WORK.DATE_ORACLE as 
Select datepart(sysdate) as sysdate From Connection to ORACLE 
(
SELECT trunc(SYSDATE) as SYS_DATE FROM DUAL
); 
quit ;

If you can't do that, then you're probably best off asking Oracle to turn the date into a text string, bringing it over, and then re-converting it back.

Oracle Dates to SAS in Passthrough SQL

1 Answers