How to extract millisecond from date in Oracle?

Question

i have full date(with time). But i want only millisecond from date.

please tell me one line solution

for example: date= 2016/03/16 10:45:04.252 i want this answer= 252

i try to use this query.

SELECT ADD_MONTHS(millisecond, -datepart('2016/03/16 10:45:04.252', millisecond),
     '2016/03/16 10:45:04.252') FROM DUAL;

but i'm not success.

Answer 1

i have full date (with time)

This can only be done using a timestamp. Although Oracle's date does contain a time, it only stores seconds, not milliseconds.

---

To get the fractional seconds from a timestamp use to_char() and convert that to a number:

select to_number(to_char(timestamp '2016-03-16 10:45:04.252', 'FF3'))
from dual;