Date in varchar2 format

[jcs1953]

I'm using this query:
SELECT REQ_PANELS.RUN_DATE, RESULTS.RESULT_NUMERIC, TESTS.TEST_NAME
FROM REQ_PANELS, REQUISITIONS, RESULTS, TESTS
WHERE REQUISITIONS.ACC_ID = REQ_PANELS.ACC_ID
AND REQ_PANELS.RP_ID = RESULTS.RP_ID
AND RESULTS.TEST_ID = TESTS.TEST_ID
AND (REQUISITIONS.PAT_ID = 'xxxxx.x')
AND (TESTS.TEST_NAME = 'GLUC')
ORDER BY REQ_PANELS.RUN_DATE

This runs fine but the Run_Date returned is 4/2/2009 12:48:17 PM. The data type for this column is varchar2(30). I need the data returned to be just mm/dd/yyyyy. TO_CHAR doesn't work as it's already CHAR. What's the best way to format this?

 

[IanWaterman]

Try

to_date(REQ_PANELS.RUN_DATE, 'MM/DD/YYYY')

Ian

 

[jcs1953]

I get ORA-01830:date format picture ends before converting entire input string

 

[jcs1953]

Figured it out.
to_char(to_date(REQ_PANELS.RUN_DATE, 'MM/DD/YYYY HH:mi:SSM'),'mm/dd/yyyy') As Run_date

 

[jimirvine]

I would very, very strongly advise you to endeavour to get the datatype of that column changed to a date. There is never a good reason to have a date stored as text in a database such as Oracle.