I realize this doesn't help Vadim right now, but it may be helpful to others who access Oracle from SAS.

The datetime values returned by SAS when accessing Oracle aren't particularly helpful in my experience ( i.e. the time portion is always zero ).

Rather than dealing with the datepart() function in SAS, I found it helpful to convert the Oracle date value to a SAS date value in the pass-thru query using a simple little macro.

Note that infld can be table.fieldname notation and, optionally, outfld can be specified if a different field name is required in the output dataset.

%macro to_sasdate(infld,outfld); %if &outfld = %str() %then %let outfld = &infld; %if %index(&outfld,.) %then %let outfld = %scan(&outfld,2,.); /* if table_name.column, strip table_name */

&infld - to_date('01jan1960','ddmonyyyy') as &outfld

%mend to_sasdate;

proc sql; connect to oracle ( ); create table saslib.sasdata as select * from connection to oracle ( select field1 ,field2 ,%to_sasdate(oracle_date_field) ,field3 from oracle_table ) ; disconnect from oracle; quit;

-----Original Message----- From: VP1695 [mailto:vp1695@AOL.COM] Sent: Friday, July 09, 2004 8:34 AM To: SAS-L@LISTSERV.UGA.EDU Subject: DATEPART function

I just wanted to warn those of you who use the DATEPART function. As many of you know, this function extracts the date from a SAS datetime value. However, if you use DATEPART on the date (not datetime) variable, the value of the function will become 0 which corresponds to 01JAN1960 in SAS. Why would anybody want to use this function on the date? Well, I'll give you a scenario. Let's say you extract data from Oracle using PROC SQL. For some reason, SAS treats Oracle dates as datetime variables, and if you want dates you may decide to use the DATEPART function in your SAS code. Now, say, at some later point your database, data warehouse,... migrates to another platform, say, Teradata. You are unaware that SAS reads Teradata dates as dates and are using the same SAS code with the DATEPART functions in it. It would be nice if SAS warned you that the argument of the DATEPART function is invalid, but SAS doesn't give either an error message or at least a warning and you are getting 01JAN1960 all over without even knowing it.

Vadim Pliner

------------------------------------------------------------This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par retour de courrier électronique ou par un autre moyen.

============================================================