I've used something similar to Darryl's approach, using %nbrquote rather than %str (I'm not sure what difference this makes). This gets around the issue that Oracle can't use double quotes, but that SAS won't decode a macro value inside single quotes.
where
History.Date between TO_DATE(%nrbquote(')&StartDateTime.%nrbquote('), 'DD:MM:YYYY:HH24:MI:SS')
and TO_DATE(%nrbquote(')&FinalDateTime.%nrbquote('), 'DD:MM:YYYY:HH24:MI:SS')
My StartDateTime and FinalDateTime macros are formatted as datetime20., but you could change this to your own format quite easily.
For example I might use:
data _null_;
call symput('StartDateTime',put(intnx('dtday',datetime(),-1),datetime20.));
call symput('FinalDateTime',put(intnx('dtday',datetime(),-1,'end'),datetime20.));
run;
%put &StartDateTime. &FinalDateTime.;