How to format a Date variable in PLSQL

Question

I am new to PL/SQL and have this question.

I created a procedure with the following specification:

PROCEDURE runschedule (i_RunDate  IN DATE)

this i_RunDate comes in a specific format, and I need to change it to this format:

'MM/dd/yyyy hh:mi:ss PM'

I couldn't find how to re-format a Date variable.

Answer 1

You need to use the TO_CHAR function. Here's an example:

SELECT TO_CHAR(CURRENT_TIMESTAMP, 'MM/DD/YYYY HH12:MI:SS AM') FROM dual;

Answer 2

The DATE has not especific format, a DATE is a DATE. When stored in the database column, date values include the time of day in seconds since midnight, and a DATE variable has the same, only that when in some point you show the variable is formatted. PL/SQL can convert the datatype of a value implicitly, for example, can convert the CHAR value '02-JUN-92' to a DATE value.. but I don't recommend you rely on this implicit conversiosn. I always use the explicit conversion. For more on this topic, you could read this: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/datatypes.htm#i9118

In your case, if you want to format your DATE to log something, or to show in your UI or wathever in a different format, you need to assig it to a varchar variable as Justin said, something like this:

....
v_generated_run_date DATE; 
v_var_date VARCHAR2(30);
BEGIN -- generate sysdate if required 
IF (i_RunDate is null) 
THEN 
v_var_date:=TO_CHAR(sysdate, 'MM/DD/YYYY HH12:MI:SS AM'); 
ELSE 
v_var_date:=TO_CHAR(i_RunDate,'MM/DD/YYYY HH12:MI:SS AM'); 
END IF; 
pkgschedule.createschedule (v_var_date); 
commit; 
END runschedule; 
END 

Your createschedule procedure, in this case, will have a varchar2 parameter...