I prefer to use functions which are usable across platforms, when possible. So to get the current time, I tried:
CAST(SYSDATE AS CHAR(19))
...but got just '07-JUL-14'. If I use Oracle's native function:
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh:mi:ss')
...I get all the detail I expect. The doc page for SYSDATE says that the value returns is a date/time. So why doesn't CAST(SYSDATE AS CHAR) return a date/time value?
I'm tempted to self-answer with "Oracle, by default, truncates dates when casting them to CHAR or VARCHAR," but I have limited Oracle experience so I'll leave the question open in case any experts can give a better answer. For example, is there some way to change this behavior? Is there some historical reason that Oracle decided to have CAST destroy information in this way?
cast uses the format defined in NLS_DATE_FORMAT to convert the DATE into a string:
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 16 00:39:11 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Last Successful login time: Wed Jul 16 2014 00:38:39 +02:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing optio SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'; Session altered. SQL> select cast(sysdate as varchar(20)) as the_date from dual; THE_DATE -------------------- 2014-07-16 00:39:27 SQL> SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi'; Session altered. SQL> select cast(sysdate as varchar(20)) as the_date from dual; THE_DATE -------------------- 16.07.2014 00:39 SQL>