How can I remove milliseconds from Timestamp in Oracle?
Solution 1
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
Solution 2
You can either cast it to a timestamp with no fractional seconds (this will round to the nearest second):
CAST( your_timestamp AS TIMESTAMP(0) )
Or to a DATE
data type (this will truncate to the nearest second):
CAST( your_timestamp AS DATE )
Or you can convert it to a formatted string and specify the format model you want to use (this will truncate to the nearest second):
TO_CHAR( your_timestamp, 'YYYY-MM-DD HH24:MI:SS' )
Like this:
Oracle 11g R2 Schema Setup:
CREATE TABLE your_table ( your_timestamp ) AS
SELECT TIMESTAMP '2017-10-25 12:53:12.10076' FROM DUAL;
Query 1:
SELECT CAST( your_timestamp AS TIMESTAMP(0) ) AS "Timestamp",
CAST( your_timestamp AS DATE ) AS "Date",
TO_CHAR( your_timestamp, 'DD-MM-YYYY HH24:MI:SS' ) AS "String"
FROM your_table
| Timestamp | Date | String |
|-----------------------|----------------------|---------------------|
| 2017-10-25 12:53:12.0 | 2017-10-25T12:53:12Z | 25-10-2017 12:53:12 |
How the TIMESTAMP
and DATE
are formatted will depend on your NLS_TIMESTAMP_FORMAT
and NLS_DATE_FORMAT
session parameters but you can directly control the formatting of TO_CHAR
when you specify a format model.
Related videos on Youtube
Thangaraj Murugananthan
Updated on July 09, 2022Comments
-
Thangaraj Murugananthan almost 2 years
I want to export data from 'My_Customer' table in .csv format.
I am extracting a time field but I am getting the output along with Milliseconds.
Eg:
The output I am getting,
25-10-2017 12:53:12:00076
I want to remove/truncate the milliseconds.Expected Output-
25-10-2017 12:53:12
.I have tried
to_char()
but it isn't giving proper output. (Instead of giving the exact time available in that field, it isrounding off
).-
Cyrille MODIANO over 6 yearsWhy don't you share the result of what you tried and what didn't work? TO_CHAR() should work
-
-
XING over 6 yearsSuch questions was already answered very well and iterating same thing doesnot make sense. Feel free to mark it duplicate.
-
XING over 6 yearsSuch questions was already answered very well and iterating same thing doesnot make sense. Feel free to mark it duplicate.