SQL Server 2005 to Oracle via Linked Server - OpenQuery Syntax with Datefields

For some reason the Oracle Linked Server in SQL Server 2005 is more "type sensitive" when it comes to data types.  We've had to resort to using OpenQuery instead of the 4 part linked server syntax and using conversions of the datatypes.  The unique part of using openquery is to get as much of the query to resolve on the host (oracle) side as possible, to do this you will need to become familiar with Oracle's functions

Between on two hard coded date fields:

Select * from openquery(TSR,'SELECT to_char(FILE_SEQ_ID) as FILE_SEQ_ID, MACHINE_NM, TO_CHAR(PATH_ID) AS PATH_ID, TUNER_ID, FILE_NM, TO_CHAR(FILE_SUFFIX_ID) AS FILE_SUFFIX_ID, TO_CHAR(PREVIOUS_FILE_SEQ_ID) AS PREVIOUS_FILE_SEQ_ID, LEAP_RECV_TS, CRE_PROC_TS, TO_CHAR(FILE_SOURCE_CD) AS FILE_SOURCE_CD from TSR1.LOAD_FILE_HIST where CRE_PROC_TS between to_date(''03/10/2008 03:00:00'',''mm/dd/yyyy hh24:mi:ss'') and to_date(''03/14/2008 03:30:00'',''mm/dd/yyyy hh24:mi:ss'')')

Between on hard coded date field with System Date.

Select @fin_Current = fintotal from openquery(FIN,'select SUM(tot_returns) as fintotal from division_vol_sum where sum_dttm between to_date(''01/01/2008 00:00:00'',''mm/dd/yyyy hh24:mi:ss'') and trunc(sysdate) and division_dept_id <> 24718')