CODEWITHSUNDEEP
sqloracle-databaseplsql
Bastian Voigt
Bastian Voigt

Reputation: 5671

Oracle - how to truncate a timestamp to milliseconds

The oracle function current_timestamp creates a timestamp with microsecond resolution, i.e. 6 decimal digits:

> select current_timestamp from dual;

2018-04-26 13:20:07.253909

How can I (using pure SQL or PL/SQL) convert this to a milliseconds resolution with only 3 decimal digits and store it in a timestamp field:

2018-04-26 13:20:07.253

Upvotes: 1

Views: 3718

Answers (2)

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

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

Upvotes: 3

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

I think the proper conversion would be

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

TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') returns a STRING but not a TIMESTAMP, i.e. for further processing like INTERVAL operations you would have to convert it back to TIMESTAMP.

Upvotes: 2

Related Questions