SQL and Dates

[Flip]

From a connection to ORACLE, if I execute the following code to retrieve data and set a timestamp. Example a sets trandate equal to the datetime, while example b correctly sets trandate to the date.

Has anyone run into this before.
Flip

a: create table work.vbrk as select *, "&sysdate"d as trandate format = date.
from ora_read.v_Zsd_vbrk order by bill_num;

b: create table work.vbrk as select *, "&sysdate"d as trandate format = date.;

[sbb]

The concept of SAS numeric variables representing each of DATE and DATETIME values are very unique and different. You will want to read up on the topic in the SAS DOC. For example, the two SAS statements below assign each of a DATA and a DATETIME variable, and show both the formatted and SAS-internal values:

DATA _NULL_;
MYDATETIME = "&SYSDATE9:&SYSTIME"DT;
MYDATE = "&SYSDATE9"D;
PUT 'Seconds since 1/1/1960' / MYDATETIME= BEST. / MYDATETIME= DATETIME. //;
PUT 'Days since 1/1/1960' / MYDATE= BEST. / MYDATE= DATE. //;
RUN;

Scott Barry
SBBWorks, Inc.

[Flip]

I don't think you understand my problem. Notice the same code is submitted for the creation of the date value. In the case where there is an ORDER BY clause, the numeric value produces an error when displayed as date..

When I format it as datetime. it returns '21JAN09:00:00:00:00' indicating that it has in fact set the numeric to the datetime rather than date.

This does not happen when accessing local libraries, only when I am pulling data from ORACLE. Adding an extra processing step solves the problem, but when processing 10**6 plus records it slows things down.

Flip

[LinusH]

This seems very odd. I can't see why your SQL shouldn't work. Probably the best way to solve this is to open a track to SAS support.

A work-around could be using a view, that shouldn't slow things down that much.

/Linus