BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tbagger70
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
DBailey
Lapis Lazuli | Level 10

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;




View solution in original post

4 REPLIES 4
DBailey
Lapis Lazuli | Level 10

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
Calcite | Level 5

Thanks  for the help dbailey. Saved me

NaveenSrinivasan
Calcite | Level 5

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
Lapis Lazuli | Level 10

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. 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2125 views
  • 0 likes
  • 3 in conversation