Informix timestamps in Oracle (via ODBC)

Question

I need to select some values from an Informix database via Oracle ODBC. One of the columns is a timestamp, and when I just select it all I see in SQL*Plus is the date value. How do I get the time as well?

Answer 1

Hi Greg,

By default SQL*Plus will display the date in the format specified by the NLS_DATE_FORMAT system parameter (client side). You can alter this behaviour by setting the NLS_DATE_FORMAT appropriately. You can also explicitly display the time data:

SQL> select sysdate from dual;

SYSDATE
-----------
05/10/2009

SQL> select to_char(sysdate, 'hh24:mi') from dual;

TO_CHAR(SYSDATE,'HH24:MI')
--------------------------
13:55