Oracle display timestamp as military time

Solution 1:

If your data type is TIMESTAMP then use:

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

If your data type is TIMESTAMP WITH TIME ZONE then use:

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

or

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

However, any user can change their own session parameters at any time so if you want a particular format then you are better to change from outputting a TIMESTAMP to outputting a string containing the value in the given format using TO_CHAR:

SELECT seq_num,
       TO_CHAR(dt, 'YYYY-MM-DD HH24:MI:SS.FF') AS dt
FROM   table_name;

db<>fiddle here

Solution 2:

You appear to have a plain TIMESTAMP column, not a TIMESTAMP WITH TIME ZONE column. (Or it could be TIMESTAMP WITH LOCAL TIME ZONE, if your session is set up as UTC/GMT.)

You are setting NLS_TIMESTAMP_TZ_FORMAT, but that applies to TIMESTAMP WITH TIME ZONE, not plain TIMESTAMP.

If you set the relevant NLS parameter instead (without the _TZ part):

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

then you get:

db<>fiddle with plain TIMESTAMP, or with TIMESTAMP WITH LOCAL TIMEZONE, which gets the same output as the session time zone is GMT.

Or you can use to_char() with the same format mask, so you aren't relying on session NLS settings.