Tag Archives: TIMESTAMP

Oracle Timestamp to Date Conversion

Some simple ways for conversion of Oracle Timestamp to Date.

/* PL-SQL */


SQL> SELECT TO_CHAR (SYSTIMESTAMP, 'YYYY-MON-DD HH24:MI:SS') AS TS FROM DUAL ;

TS
----------------------------
2012-JAN-24 19:06:18

SQL> SELECT TO_DATE (TO_CHAR (SYSTIMESTAMP, 'YYYY-MON-DD HH24:MI:SS'), 'YYYY-MON-DD HH24:MI:SS' ) AS DT FROM DUAL ;

DT
----------------
24-JAN-12


SQL> SELECT SYSDATE DT FROM DUAL ;
 
DT
---------
24-JAN-12
 
SQL> SELECT SYSTIMESTAMP TS FROM DUAL;
 
TS
-------------------------------------------------------
24-JAN-12 07.08.02.283311000 PM +05:00
 
SQL> SELECT CAST(SYSTIMESTAMP AS DATE) DT FROM DUAL;
 
DT
--------------
24-JAN-12
 
SQL> SELECT TRUNC(SYSTIMESTAMP) DT FROM DUAL;
 
DT
--------------
24-JAN-12