removing milliseconds from a oracle tmstmp field

Question

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!

Answer 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))

Answer 2

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;

SQL FIDDLE