Default Timestamp Format
I just set the default timestamp format as
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF'
When I insert data into the table, the timestamp is inserted as
0014-08-11 04:45:24.000000000
When I request
SELECT SYSTIMESTAMP FROM DUAL
I get:
11-AUG-14 06.14.58.400000000 PM +04:00
But I need a default timestamp like 2014-07-22 05: 54: 18.000000000.
It would be nice if someone could help me with this.
source to share
SYSTIMESTAMP
returns data type TIMESTAMP WITH TIMEZONE
. Thus, you just need to send it to a regular one TIMESTAMP
:
SELECT CAST(SYSTIMESTAMP AS TIMESTAMP) FROM DUAL;
CAST(SYSTIMESTAMPASTIMESTAMP)
-----------------------------
2014-08-11 15:27:11.091862000
Or set a separate parameter NLS_TIMESTAMP_TZ_FORMAT
:
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 are losing time zone information that may or may not matter to you.
But this has nothing to do with querying values from a column TIMESTAMP
(no timezone) in your table. The value in the table is not formatted by accident; NLS settings on insert do not affect how they are stored or how they are displayed when requested. You need to specify the format at query time as well as at insert time - and it is preferable to use explicit format models with TO_TIMESTAMP()
and TO_CHAR()
rather than respond to NLS settings that you might not be able to control.
You should also use HH24
as you no longer have the AM / PM marker.
source to share