TO_DATE removes time stamp from VARCHAR2
914837Feb 2 2012 — edited Feb 2 2012Hi Guys,
I am facing this strange problem - when I convert a varchar value with time stamp to a date, it removed the time stamp. I need the timestamp as it is in Date type. Could you help please.
--------------------------------------------------------------------------------
DECLARE
gv_creation_date_from VARCHAR2 (150);
pi_creation_date_from VARCHAR2 (150);
BEGIN
pi_creation_date_from := '2012/01/17 08:14:26';
DBMS_OUTPUT.PUT_LINE ( 'pi_creation_date_from = ' || pi_creation_date_from );
gv_creation_date_from :=
TO_DATE (pi_creation_date_from, 'YYYY/MM/DD HH24:MI:SS');
DBMS_OUTPUT.PUT_LINE ( 'gv_creation_date_from = ' || gv_creation_date_from );
END;
--------------------------------------------------------------------------------
DBMS OUTPUT
pi_creation_date_from = 2012/01/17 08:14:26
gv_creation_date_from = 17-JAN-12
--------------------------------------------------------------------------------
NLS_DATE_FORMAT definition is
SELECT isdefault, VALUE, TYPE
FROM v$parameter
WHERE name = 'nls_date_format'
--------------------------------------------------------------------------------
FALSE DD-MON-RR 2
Appricate your help, thanks in advance.
- Prayas