How do I translate sql to_char to sas code?

[tbagger70]

I'm trying to compare a datefield in an oracle table. Because of the field format, I have not been able to figure out a way to compare. I could really use some help.

The field data looks like this (named = Activity_attempt_dttm);

11JUN2008:16:06:09.111000

12JUN2008:13:54:18.749000

This is the basic sql I’m trying to use;

SELECT login_user_id, activity_attempt_dttm, audit_remark_txt

FROM MAPTAB.SESA_SECURITY_AUDIT

WHERE activity_attempt_dttm = "12JUN2008:13:54:18.749000";

The final solution for me is to be able to select data between a from date and a to date. I only need to compare the dates, not the time.

Any help would be greatful.

tbagger70

[DBailey · Accepted solution]

Most SAS installations accessing oracle dates use a SAS datetime variable and the sas connector translates between SAS and Oracle requirements.  So..for a column that is defined as a date in Oracle you would simply use a datetime SAS variable:

SELECT login_user_id, activity_attempt_dttm, audit_remark_txt

FROM MAPTAB.SESA_SECURITY_AUDIT

WHERE activity_attempt_dttm = "12JUN2008:13:54:18.749000"dt;

or

SELECT login_user_id, activity_attempt_dttm, audit_remark_txt

FROM MAPTAB.SESA_SECURITY_AUDIT

WHERE activity_attempt_dttm GE'01Jun2008:0:0'dt

     and activity_attempt_dttm LT '01Jul2008:0:0'dt;

[tbagger70]

Thanks  for the help dbailey. Saved me

[NaveenSrinivasan]

Hi,

Since you have mentioned that you want to select and filter only the Date and exclude the time, I believe you would need to apply the DATEPART function to extract the date and exclude the time, and then apply the filter.

Thanks,

Naveen

[DBailey]

You can do that..but if you do SAS won't be able to pass the query to Oracle for execution.  SAS would then pull all of the necessary information down locally to decide if a row met the datepart criteria.