Oracle display timestamp as military time-CodePudding

I have a timestamp column in one of my tables. I am doing the following and its displayed as 12.00.00.100000 AM but when the time passes 12AM its displayed in military time as I want it.

How can I display the date as military time 12AM time as 00:00:00.100000


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

SEQ_NUM    DT
1    01-JAN-22 12.00.00.000000 AM
2    01-JAN-22 12.05.00.100000 AM
3    01-JAN-22 12.10.00.200000 AM
4    01-JAN-22 12.15.00.300000 AM
5    01-JAN-22 12.20.00.400000 AM
6    01-JAN-22 12.25.00.500000 AM
7    01-JAN-22 12.30.00.600000 AM
8    01-JAN-22 12.35.00.700000 AM
9    01-JAN-22 12.40.00.800000 AM
10    01-JAN-22 12.45.00.900000 AM
11    01-JAN-22 12.50.00.000000 AM
12    01-JAN-22 12.55.00.100000 AM
13    01-JAN-22 01.00.00.200000 AM
14    01-JAN-22 01.05.00.300000 AM

CodePudding user response：

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';

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

CodePudding user response：

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:

SEQ_NUM	DT
1	2022-01-01 00:00:00.000000
2	2022-01-01 00:05:00.100000
3	2022-01-01 00:10:00.200000
4	2022-01-01 00:15:00.300000
5	2022-01-01 00:20:00.400000
6	2022-01-01 00:25:00.500000
7	2022-01-01 00:30:00.600000
8	2022-01-01 00:35:00.700000
9	2022-01-01 00:40:00.800000
10	2022-01-01 00:45:00.900000
11	2022-01-01 00:50:01.000000
12	2022-01-01 00:55:01.100000
13	2022-01-01 01:00:01.200000
14	2022-01-01 01:05:01.300000

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.