Removing Milliseconds from a Oracle Tmstmp Field

How can I remove milliseconds from Timestamp in Oracle?

Convert your timestamp to a character string:

SQL> SELECT TO_CHAR (SYSTIMESTAMP, 'YYYY-MON-DD HH24:MI:SS') AS my_date
  FROM DUAL;

MY_DATE
-----------------------------
2017-OCT-13 00:38:26

Removing the millisecond from the timestamp

select to_char(to_timestamp('08/01/2016 09:16:47.000000000 AM', 'MM/DD/YYYY HH:MI:SS.FF AM'),'MM/DD/YYYY HH:MI:SS AM') from dual;

Edit: This works for am or pm

select to_char(to_timestamp('08/01/2016 09:16:47.000000000 PM', 'MM/DD/YYYY HH:MI:SS.FF AM'),'MM/DD/YYYY HH:MI:SS AM') from dual;

Oracle - how to truncate a timestamp to milliseconds

SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') FROM dual;

OR

SELECT CAST(CURRENT_TIMESTAMP AS TIMESTAMP(3)) FROM dual;

Output

TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DDHH24:MI:SS.FF3')
-----------------------------------------------------
2018-04-26 13:26:16.642

How to trim milliseconds of a timestamp field in oracle

You might try the following:

CREATE VIEW myview AS
SELECT <other_columns>
     , CAST(crte_dtm AS TIMESTAMP(3)) AS crte_dtm
     , CAST(end_dtm AS TIMESTAMP(3)) AS end_dtm
  FROM employee;

Hope this helps.

Oracle: Select Milliseconds from Timestamp Type that Defaults with SYSTIMESTAMP

The precision for timestamp columns can be set up by

TIMESTAMP [(fractional_seconds_precision)].

In your case for 4 it's:

create table my_table
(id number,
  my_TIMESTAMP timestamp(4) default SYSTIMESTAMP);

You can check your current precision by:

select column_name, data_scale from user_tab_columns 
where table_name = 'MY_TABLE' and column_name = 'MY_TIMESTAMP';

Here is sample in SQL Fiddle

The display can be change by:

 alter session set NLS_TIMESTAMP_FORMAT = 'DD-MON-RRRR HH:MI:SS.FF9';

---

Related Topics