Oracle problems with DATEs

Question

Hi Folks,

iam confused. Iam trying to read an Value from an oracle table, format: timestamp(6).

In my PHP Scripts im setting the Dateformat with:

$db->query('ALTER SESSION SET NLS_DATE_FORMAT = "DD-MM-RR"');

On 2 Machines im recieving this string as value (wich is correct for me): ["TIME_INSERT"] => string(24) "05.10.07 14:20:05,000000"

On one linux machines where the same script is running, it returns: ["TIME_INSERT"] => string(28) "05-OCT-07 02.20.05.000000 PM"

any ideas how to change this ?

Answer 1

Hi ArneRie,

you should set the session parameter NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT to display timestamp data as you want:

SQL> select systimestamp from dual;

SYSTIMESTAMP
----------------------------------------------------
12/10/09 12:52:41,462532 +02:00

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'dd.mm.rr hh24:mi:ss,ff5';

Session altered

SQL> select systimestamp from dual;

SYSTIMESTAMP
--------------------------------------------------------------------------------
12.10.09 12:56:36,14023