I am trying to create a sql with prompts on a DATETIME field, however, I want to convert to just to be able to handle just date. I don't want users to have to enter in the hour and min when the prompt appears. Is there some kind of expression I can create that will convert "12/02/06" to "12/02/06 12:00AM". Thank you in advance.
SELECT A.AN_MT_ID, A.DESCR, A.AN_MT_MODULE, B.OPRDEFNDESC, A.AN_MT_STATUS, TO_CHAR(A.CREATED_DTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'), TO_CHAR(A.DTTM_CLOSED,'YYYY-MM-DD-HH24.MI.SS."000000"'), C.OPRDEFNDESC, TO_CHAR(A.COMPLETION_DATE,'YYYY-MM-DD')
FROM PS_AN_MT_HDR A, PSOPRDEFN B, PSOPRDEFN C, PS_AN_MT_ASSIGN_TO D
WHERE A.CONTACT_ID = B.OPRID
AND D.AN_MT_ID = A.AN_MT_ID
AND D.ASSIGNED_TO = C.OPRID
AND A.AN_MT_STATUS = :1
AND A.DTTM_CLOSED > TO_DATE(SUBSTR(:2, 0, 19),'YYYY-MM-DD-HH24.MI.SS')