removing milliseconds from a oracle tmstmp field
Solution 1
How about this?
select cast(col as timestamp(0))
EDIT:
The easiest way to avoid rounding is to use trunc()
or to subtract half a second:
select cast(col - 0.5/(24*60*60) as timestamp(0))
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 )
If you want it as a TIMESTAMP(0)
data type then cast it back:
CAST( CAST( your_timestamp AS DATE ) AS TIMESTAMP(0) )
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 |
note: How the TIMESTAMP
and DATE
are formatted in the output 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.
Solution 3
try this
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"
FROM DUAL;
if you need 12-hour date format
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH:MI:SS AM') "NOW"
FROM DUAL;
Related videos on Youtube
user3022875
Updated on June 04, 2022Comments
-
user3022875 almost 2 years
I have a TIMESTAMP(6) field in Oracle and I need to remove the millisecond component from the time.
For example I have
10/20/2014 10:34:06.356000 AM
and I would like to remove the milliseconds so that I have
10/20/2014 10:34:06 AM
Do you know the best way to do this?
Thank you!
-
user3022875 over 9 yearsthis works but it does rounding. Is there a way to make it not round and just remove the milliseconds without rounding up or down?
-
user3022875 over 9 yearsthis works but I need the filed to be a timestamp(0). when I run your code I lose the AM/PM and the time loses its GMT format. Can it be cast to a timestamp(0)? then it might work
-
HaveNoDisplayName over 9 yearsif you need 12 hrs format with AM/PM then use the second query
-
user3022875 over 9 yearsbut this is still a CHAR. I need it timestamp(0). is that possible?