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.

Solution

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;

Comments

[John Doe]

code only answers, while being correct, are not really useful to understand the solution. Please consider adding some text explaining what you have done and what the code means

[Nikunj Chavda]

Thanks you for efforts.i got my answer

[Seymour]

This is the cleanest answer.

[Dai]

What about EXTRACT( timestamp_value, MILLISECOND ) ? (EDIT: Ah, that's only in "Oracle Big Data", not Oracle Database, even in version 21c, that's crazy...)

[Dai]

I noticed that this works in Oracle 11g: SELECT ( timestamp_value - CAST( ValidFrom AS timestamp(0) ) ) AS ms FROM tableName however the type of ms is still timestamp(n).