How to handle date in sas while using passthrough from oracle

[anirudhs]

Hi  how to handle the below date format in sas 

07APR2019:21:28:58.00865

as fetching this data from an oracle table using a pass-through code. Where the date variable New_date is used as filter 

e.g  New_date between to_date(&start_dt, "DDMONYYYY:HR24:MI:SS") and  to_date(&end_dt, "DDMONYYYY:HR24:MI:SS") 

If you closely look to date the data post decimal is creating fetching issue.

[ChrisNZ]

what would you like the oracle string to be?

[anirudhs]

In oracle table ABC the variable New_date contains 07APR2019:21:28:58.00865 this data.
when i am using passthrough in sas extracting data from ABC extracting the data between two dates so using WHERE NEW_DATE between to_date(&start_dt, "DDMONYYYY:HR24:MI:SS") and to_date(&end_dt, "DDMONYYYY:HR24:MI:SS") but when the code is executing it is running for infinite time. and the required data is 07APR2019:21:28:58 or only date 07APR2019

[ChrisNZ]

If you just want to change the macro string, you can do that. For example:

%let a=07APR2019:21:28:58.01865;

%put b=%scan(&a,1,.);

%put c=%sysfunc(prxchange(s/(.*\.\d\d).*/\1/,1,&a));

b=07APR2019:21:28:58
c=07APR2019:21:28:58.01

You could even just use %substr()  since the length is aways known

[anirudhs]

the macro contains the time :hh:mm:ss .. when the code goes for scanning it gets 07APR2019:21:28:58.00865 in data so the code is keeping on executing.

[Tom]

I don't understand what code you are trying to run.

What does the MACRO variable referenced in you ORACLE statement contain?  Is it the name of an variable in the Oracle query? Something else?

Show the ORACLE code that works.

Explain what part of the ORACLE code you want to generate using SAS macro code.

[anirudhs]

the macro variable
auth_st_dt = intnx('Month',today(), -1,'b');
auth_end_dt= intnx('Month',today(), -1,'e');
call symput ('auth_st_dt' , "'" || put(auth_st_dt, date9.) || ":00:00:00'");
call symput ('auth_end_dt', "'" || put(auth_end_dt, date9.) || ":23:59:59'");

[SASKiwi]

Please post the complete SAS log of your Oracle query so we can fully understand what you are doing. Just posting bits of what you are doing isn't useful.

[Tom]

---

@anirudhs wrote:
the macro variable
auth_st_dt = intnx('Month',today(), -1,'b');
auth_end_dt= intnx('Month',today(), -1,'e');
call symput ('auth_st_dt' , "'" || put(auth_st_dt, date9.) || ":00:00:00'");
call symput ('auth_end_dt', "'" || put(auth_end_dt, date9.) || ":23:59:59'");

---

So you are creating macro variables that have in them strings in the style generated by the DATETIME format that are enclosed in single quotes.

%let auth_st_dt = '08APR2022:00:00:00' ;
%let auth_end_dt = '30APR2022:23:59:59';

Then in the ORACLE code you inserting that string. So to ORACLE that looks like a string constant.

Is the field in ORACLE a DATE or a TIMESTAMP?