PL/SQL date problem !

[larva]

Hi guys,

My table A contain a column called VDATE with date as datatype. The data in this column is like 15-Jun-2002 01:27:53 PM. My procedure is as follow :

(

str_date IN A.vdate%TYPE)

AS

v_date VARCHAR2(30);

BEGIN

v_filename := TO_CHAR(SYSDATE,'yyyymmddhh24misssss');
fileHandler := UTL_FILE.FOPEN('/var/ltemp/', v_filename || '.bin' , 'w');
UTL_FILE.PUTF(fileHandler,'%s',str_date);
UTL_FILE.FCLOSE(fileHandler);
END IF;
EXCEPTION

WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');

End;

When I open the file generated by this script, the date appear to be like 15-Jun-2002 only without the time ! I need to include the time also ! Pls help !

regards,
Larva

 

[Jeraden]

Its doing an implicit date to string conversion since putf is expecting a string to come in.

Do this instead:
UTL_FILE.PUTF(fileHandler,'%s',to_char(str_date,'DD-MON-YYYY HH:MI:SS PM'));