Friday, 13 June 2008

Oracle Quick Tip: Setting the Default Date and Timestamp Format for a Session

The default DATE, TIMESTAMP and TIMESTAMP WITH TIME ZONE format models for a session can be altered with the following commands.
ALTER SESSION SET nls_date_format = [format model];
ALTER SESSION SET nls_timestamp_format = [format model];
ALTER SESSION SET nls_timestamp_tz_format = [format model];
For example.
ALTER SESSION SET nls_date_format='DD-Mon-RR HH24:MI:SS';
ALTER SESSION SET nls_timestamp_format='DD-Mon-RR HH24.MI.SSXFF3';
ALTER SESSION SET nls_timestamp_tz_format='DD-Mon-RR HH:MI.SSXFF6 TZR';

SELECT current_date, localtimestamp, current_timestamp FROM dual;

CURRENT_DATE LOCALTIMESTAMP CURRENT_TIMESTAMP
------------------- ----------------------- ----------------------------
09-Feb-09 12:57:43 09-Feb-09 12.57.42.787 09-Feb-09 12:57.42.787953 GB

1 rows selected
Alternatively, if you are using SQL Developer, select Tools -> Preferences, expand Database and select NLS Parameters.

The V$NLS_PARAMETERS view contains the current values of the NLS parameters.
SELECT *
FROM v$nls_parameters
WHERE parameter IN ('NLS_DATE_FORMAT', 'NLS_TIMESTAMP_FORMAT', 'NLS_TIMESTAMP_TZ_FORMAT');
PARAMETER                 VALUE
------------------------- -----------------------------
NLS_DATE_FORMAT DD-Mon-RR HH24:MI:SS
NLS_TIMESTAMP_FORMAT DD-Mon-RR HH24.MI.SSXFF3
NLS_TIMESTAMP_TZ_FORMAT DD-Mon-RR HH24:MI.SSXFF TZR

3 rows selected

1 comment:

buyi wen said...

you can try this free online timestamp converter to get a unix time.