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
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;
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;
Thanks for the help dbailey. Saved me
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
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.