CODEWITHSUNDEEP
sqloracle-databasetimestamp
SitGeek
SitGeek

Reputation: 45

How to trim milliseconds of a timestamp field in oracle

I have a table EMPLOYEE and it has two TIMESTAMP fields "CRTE_DTM" and "END_DTM".

I would like to create a view on top EMPLOYEE table where I can put some function on these two fields to return TIMESTAMP with only 3 precision of milliseconds.

Example:

If I have below row in EMPLOYEE table

CRTE_DTM
----------------
10-SEP-02 02.10.10.123000000 PM

I would like to trim the timestamp to have only 3 precision in milliseconds, see below,

CRTE_DTM
----------------
10-SEP-02 02.10.10.123 PM

Note: I'm using this view to load data into a table where "CRTE_DTM" and "END_DTM" fields are TIMESTAMP's

Upvotes: 1

Views: 7077

Answers (4)

santos
santos

Reputation: 1

select localtimestamp(9) from dual;

Upvotes: -2

ewramner
ewramner

Reputation: 6233

If you can recreate the table (or add columns, copy data, drop the old and rename) you can use the datatype TIMESTAMP(3). The default for TIMESTAMP is 6 fractional digits, but it can be overridden.

Alternatively you can convert to a string with a specified format and (optionally if you want to work with TIMESTAMP) back again:

select to_timestamp(
    to_char(crte_dtm, 'YYYY-MM-DD HH24:MI:SS.FF3'),
    'YYYY-MM-DD HH24:MI:SS.FF3') from employee;

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

10-SEP-02 02.10.10.123000000 PM is the same timestamp as 10-SEP-02 02.10.10.123 PM. What you can do is select a string showing this timestamp in the desired format:

select to_char(timestamp_column, 'dd-MON-yy hh24.mi.ss.ff3', 'nls_date_language=english')
from mytable;

Upvotes: 0

David Faber
David Faber

Reputation: 12485

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.

Upvotes: 1

Related Questions