CODEWITHSUNDEEP
oracle-databasesas
Netbrian
Netbrian

Reputation: 641

Oracle Dates to SAS in Passthrough SQL

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 ;

Upvotes: 1

Views: 2531

Answers (1)

Joe
Joe

Reputation: 63424

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.

Upvotes: 1

Related Questions