SELECT MAX(TO_CHAR(f_ra_KWH1_reading_taken,v_dateformat)) INTO v_date
FROM t_rdg_abs_KWH1 WHERE f_ra_KWH1_reading_taken <= TO_DATE(p_start_date,v_dateformat) 
AND f_ra_KWH1_validated = 1 AND f_ra_KWH1_fk_mp_id = TO_NUMBER(p_meter_profile_id)';

strsql := 
'SELECT MAX(TO_CHAR(f_ra_KWH1_reading_taken,v_dateformat)) 
   FROM t_rdg_abs_KWH1 
  WHERE f_ra_KWH1_reading_taken <= TO_DATE  (p_start_date,v_dateformat) 
AND f_ra_KWH1_validated = 1
AND f_ra_KWH1_fk_mp_id = TO_NUMBER(p_meter_profile_id)';
Execute immediate strsql INTO v_date;

 SELECT MAX(TO_CHAR(f_ra_KWH1_reading_taken,MM/DD/YYYY HH:MI:SS AM)) FROM t_rdg_abs_KWH
 WHERE f_ra_KWH1_reading_taken <= TO_DATE(1/23/2008 3:00:00 PM,MM/DD/YYYY HH:MI:SS AM)
 AND f_ra_KWH1_validated = 1 ;

strsql := 'SELECT MAX(TO_CHAR(f_ra_'||v_dut_desc||'_reading_taken,'||v_dateformat||'))
               FROM t_rdg_abs_'||v_dut_desc||'
              WHERE f_ra_'||v_dut_desc||'_reading_taken <= TO_DATE('||p_start_date||','||v_dateformat||')
                AND f_ra_'||v_dut_desc||'_validated = 1';

strsql := 'SELECT MAX(TO_CHAR(f_ra_'||v_dut_desc||'_reading_taken,'||v_dateformat||'))
               FROM t_rdg_abs_'||v_dut_desc||'
              WHERE f_ra_'||v_dut_desc||'_reading_taken <= TO_DATE('||p_start_date||','||v_dateformat||')
                AND f_ra_'||v_dut_desc||'_validated = 1';

SELECT MAX(TO_CHAR(f_ra_KWH1_reading_taken,MM/DD/YYYY HH:MI:SS AM)) FROM t_rdg_abs_KWH
 WHERE f_ra_KWH1_reading_taken <= TO_DATE(1/23/2008 3:00:00 PM,MM/DD/YYYY HH:MI:SS AM)
 AND f_ra_KWH1_validated = 1 ;

1/23/2008 3:00:00 PM,MM/DD/YYYY HH:MI:SS AM

strsql := 'SELECT MAX(TO_CHAR(f_ra_kwh1_reading_taken,:1))
               FROM t_rdg_abs_kwh1
              WHERE f_ra_kwh1_reading_taken <= TO_DATE(:2,:1)
                AND f_ra_kwh1_validated = 1
                AND f_ra_kwh1_fk_mp_id = TO_NUMBER(:3)';
DBMS_OUTPUT.PUT_LINE(strsql);
      EXECUTE IMMEDIATE strsql USING v_dateformat, v_start_date, p_profile_id;

EXECUTE IMMEDIATE strsql USING v_dateformat, v_start_date, v_dateformat, p_profile_id;

TEST1 FOO>create table foo (foo datE);

Table created.

TEST1 FOO>insert into foo values (to_date('12/31/1955','mm/dd/yyyy'));

1 row created.

TEST1 FOO>insert into foo values (to_date('01/01/2008','mm/dd/yyyy'));

1 row created.

TEST1 FOO>select max(to_char(foo,'mm/dd/yyyy')) from foo;

MAX(TO_CHA
----------
12/31/1955

EXECUTE IMMEDIATE yourstring INTO your_variable(s) USING your_other_variable(s)

The result string is :
SELECT TO_CHAR(MAX(f_ra_KWH1_reading_taken),'MM/DD/YYYY HH:MI:SS AM') FROM
t_rdg_abs_KWH1 WHERE f_ra_KWH1_reading_taken <= TO_DATE('1/23/2008 3:00:00
PM','MM/DD/YYYY HH:MI:SS AM') AND f_ra_KWH1_validated = 1  AND
f_ra_KWH1_fk_mp_id = TO_NUMBER(6748);

BEGIN pk_substitution.sp_substitute('1/23/2008 3:00:00 PM','1/23/2008 4:30:00 AM',6748); END;

*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "NOC_TEST.PK_SUBSTITUTION", line 138
ORA-06512: at "NOC_TEST.PK_SUBSTITUTION", line 55
ORA-06512: at line 1

strsql := 'SELECT TO_CHAR(MAX(f_ra_'||v_dut_desc||'_reading_taken),''MM/DD/YYYY HH:MI:SS AM'')'
               ||' FROM t_rdg_abs_'||v_dut_desc||
              ' WHERE f_ra_'||v_dut_desc||'_reading_taken <= '
              ||'TO_DATE('''||p_start_date||''',''MM/DD/YYYY HH:MI:SS AM'')'
               ||' AND f_ra_'||v_dut_desc||'_validated = 1 '
                ||' AND f_ra_'||v_dut_desc||'_fk_mp_id = TO_NUMBER('||p_meter_profile_id||')'||';';