This content has been marked as final. Show 5 replies
you have declared v_latest_close as DATE change it to timestamp and try
Try this you will get exactly what you want...
DECLARE v_latest_close timestamp; BEGIN v_latest_close := TO_DATE ('12/31/2012 23:59:59 ','MM/DD/YYYY HH24:MI:SS'); DBMS_OUTPUT.PUT_LINE('The new date format is : '|| to_char(v_latest_close,'dd/mm/yyyy hh:mi:ss am')); END;
In the tool you are using issue alter session set nls_date_format='dd/mm/yyyy hh:mi:ss am' and run your anonymous block.
Try this... and NO need to change the variable from DATE to TIMESTAMP.
'v_latest_close' had the string '12/31/2012 23:59:59' converted into DATE format (using TO_DATE).
But, DATE even though internally is stored in different way, when displayed it gets formatted as per your NLS settings.
So, just for sake of display, we used TO_CHAR with the desired Format String.
DECLARE v_latest_close DATE; BEGIN v_latest_close := TO_DATE ('12/31/2012 23:59:59 ','MM/DD/YYYY HH24:MI:SS'); DBMS_OUTPUT.PUT_LINE('The new date format is : '|| to_char(v_latest_close,'mm/dd/yyyy hh12:mi:ss AM')); END;
The new date format is : 12/31/2012 11:59:59 PM
TIMESTAMP is needed to hold 'extra' DATE attributes like - Fractional Seconds, TIMEZONE, etc.
In this case, DATE will suffice. It internally stores the 'Time' details and only is matter of display using TO_CHAR.
Hope that Helps,