Convert varchar2 format to datetime format

[gargkapil2008]

i want to find records between two dates IN ORCALE but the table field values is in "mm/dd/yy hh:mm::ss" and data type is varchar2
The query which i was trying was:

SELECT * FROM NEW_OUTPUT_UPLOAD_TSN
WHERE OUTPUT_UPLOAD_DATE BETWEEN TO_DATE('01/13/2008 18:45:19','MM/DD/YYYY 24HH:MI:SS')
 AND TO_DATE('01/13/2008 18:45:19','MM/DD/YYYY 24HH:MI:SS')

[hfx642]

Try...

SELECT * FROM NEW_OUTPUT_UPLOAD_TSN
WHERE To_Date (OUTPUT_UPLOAD_DATE, 'MM/DD/YY HH24:MI:SS')
   BETWEEN TO_DATE('01/13/2008 18:45:19','MM/DD/YYYY HH24:MI:SS')
   AND TO_DATE('01/13/2008 18:45:19','MM/DD/YYYY HH24:MI:SS')

However, Your "between" dates are the same... NOT a range.
Is this correct?
Also... Your 24 comes AFTER the HH... NOT before.

For future reference, never use a VarChar2 datatype for a date.
You're just asking for problems like you're having now.