Removing milliseconds from oracle field tmstmp

Question

I have a TIMESTAMP (6) field in Oracle and I need to remove the millisecond component from the point in 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?

Thanks!

Answer 1

How about this?

 select cast(col as timestamp(0)) 

EDIT:

The easiest way to avoid rounding is to use trunc() or subtract half a second:

 select cast(col - 0.5/(24*60*60) as timestamp(0)) 

Answer 2

try it

 SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL; 

if you need a 12 hour date format

 SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH:MI:SS AM') "NOW" FROM DUAL; 

SQL FIDDLE

Answer 3

You can either put it on a timestamp without fractional seconds (this will be rounded to the nearest second):

 CAST( your_timestamp AS TIMESTAMP(0) ) 

Or to the DATE data type (this will be truncated to the nearest second):

 CAST( your_timestamp AS DATE ) 

If you want it to be like a TIMESTAMP(0) data TIMESTAMP(0) , then discard it:

 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 be truncated to the nearest second):

 TO_CHAR( your_timestamp, 'YYYY-MM-DD HH24:MI:SS' ) 

Like this:

SQL Fiddle

Oracle 11g R2 schema setup :

 CREATE TABLE your_table ( your_timestamp ) AS SELECT TIMESTAMP '2017-10-25 12:53:12.10076' FROM DUAL; 

Request 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 

Results :

 | 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 output are formatted depends on the NLS_TIMESTAMP_FORMAT and NLS_DATE_FORMAT session parameters, but you can directly control the TO_CHAR formatting when specifying the format model.

Answer 4

To find records with the same date / time accurate to the second, but with different milliseconds, you can compare different records by attaching the table to yourself.

 SELECT A.ts, B.ts FROM Test A INNER JOIN Test B ON TO_CHAR(A.ts, 'YYYY-MM-DD HH24:MI:SS') = TO_CHAR(B.ts, 'YYYY-MM-DD HH24:MI:SS') WHERE A.ts < B.ts ORDER BY A.ts, B.ts; 

TO_CHAR milliseconds. This is important because the functions of this round can take different seconds. For example, CAST(ts as timestamp(0)) rounds, but this is not what you need.

The example in the link below has a record with 999 milliseconds to verify this.

See an example on SQL Fiddle .

Answer 5

This can help!

 select substr(to_char('10/20/2014 10:34:06.356000 AM'),1,instr(to_char('10/20/2014 10:34:06.356000 AM'),'.')-1)||' '|| substr(to_char('10/20/2014 10:34:06.356000 AM'),-2,instr(to_char('10/20/2014 10:34:06.356000 AM'),'.')-1) "Date" from dual;