How to format a Date variable in PLSQL

Question

I am new to PL / SQL and ask 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 in this format:

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

I could not find a way to reformat the Date variable.

Answer 1

DATE does not have a special format, DATE - DATE. When stored in a database column, the date values ​​include the time of day in seconds since midnight, and the DATE variable has the same value only when at some point you indicate that the variable is being formatted. PL / SQL can convert the data type of a value implicitly, for example, it can convert a CHAR value of '02 -JUN-92 'to a DATE value .. but I do not recommend that you rely on this implicit dialog. I always use explicit conversion. You can read more about this topic: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/datatypes.htm#i9118

In your case, if you want to format DATE to register something or show it in your user interface or in any other format in another format, you need to specify the 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 schedule creation procedure in this case will have the varchar2 parameter ...

Answer 2

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

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