Trying to remove the milliseconds and the pm/am from the datetime field using Oracle SQL.

My query is:

select created_dt from mydatabase

Output is:

09-NOV-21 12.18.40.490000000 PM

I tried using to_char(created_dt, 'dd-mm-yy hh24:mi:ss') then using to_timestamp to convert back to datetime type but the milliseconds showing again.

CodePudding user response：

A TIMESTAMP data type is stored in a table as a binary data-type with 1-byte for each of century, year-of-century, month, day, hour, minute and second and has up to 6 bytes for fractional seconds. It is NEVER stored with any particular format.

If you want to change the number of fractional digits in a timestamp then you need to use CAST:

SELECT CAST(created_dt AS TIMESTAMP(0))
FROM   mydatabase

However, you could just convert it to a DATE (which also has year-to-second components but no fractional seconds):

SELECT CAST(created_dt AS DATE)
FROM   mydatabase

Once you have done that then whatever client application you are using (SQL/Plus, SQL Developer, Java, C#, PHP, etc.) will still use its default rules for displaying the TIMESTAMP (or DATE) and those rules may display a certain number of fractional seconds.

If you are using SQL/Plus or SQL Developer (but not if you are using Java, C#, PHP, etc.) then the default format is controlled by the NLS_TIMESTAMP_FORMAT session parameter.

You can alter this using:

-- Display with precision of the data type
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
-- Display with 6-digits precision
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF6';
-- Display with no fractional seconds.
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

But, if you want a consistent method of formatting a TIMESTAMP then use TO_CHAR.

CodePudding user response：

How the DATE or TIMESTAMP data types are stored can't be changed.

But you can change how a query outputs a TIMESTAMP by default for the session.

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

Example:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MON-DD HH24:MI:SS';

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

select ts
, CAST(ts AS DATE) dt
, CAST(ts AS TIMESTAMP WITH TIME ZONE) AS tstz
from
(
  select 
   to_timestamp('2021-11-30 23:30:45.123456789', 'YYYY-MM-DD HH24:MI:SS.FF') AS ts     
  from dual
) q

TS                  | DT                   | TSTZ                            
:------------------ | :------------------- | :-------------------------------
2021-11-30 23:30:45 | 2021-NOV-30 23:30:45 | 30-NOV-21 23.30.45.123457  00:00

db<>fiddle here