Oracle default Timestamp format

Question

I just set default timestamp format as

ALTER SESSION SET  NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF'

When I insert the data into the table the timestamp inserted as,

0014-08-11 04:45:24.000000000

When I query

SELECT SYSTIMESTAMP FROM DUAL

I get:

11-AUG-14 06.14.58.400000000 PM +04:00

But I want the default timestamp as 2014-07-22 05:54:18.000000000.

It would be appreciated if some one could help me on this.

Answer 1

SYSTIMESTAMP returns data type TIMESTAMP WITH TIMEZONE. So you either need to cast it to a plain TIMESTAMP:

SELECT CAST(SYSTIMESTAMP AS TIMESTAMP) FROM DUAL;

CAST(SYSTIMESTAMPASTIMESTAMP)
-----------------------------
2014-08-11 15:27:11.091862000 

Or set the separate NLS_TIMESTAMP_TZ_FORMAT parameter:

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';

SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP         
----------------------
2014-08-11 15:27:11.35 
1526000                

Either way you're losing the time zone information, which may or may not matter to you.

But this doesn't have anything to do with querying values from a TIMESTAMP (without time zone) column in your table. The value in the table has no format incidentally; the NLS settings when you insert will not affect how it is stored or how it is displayed when queried. You need to specify the format at query time as well as at insert time - and preferably using explicit format models with TO_TIMESTAMP() and TO_CHAR() rather than replying on NLS settings, which you might not be able to control.

You should also be using HH24 since you no longer have the AM/PM marker.