CODEWITHSUNDEEP
sqloracle11gtimestamp
Nikunj Chavda
Nikunj Chavda

Reputation: 47

How to extract millisecond from date in Oracle?

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.

Upvotes: 4

Views: 46258

Answers (3)

Ibrahim_Al_Sabath
Ibrahim_Al_Sabath

Reputation: 1

The millisecond of the day:

  select to_number(to_char(tms_now, 'sssss') || to_char(tms_now, 'ff3')) day_millisecond
    from (select systimestamp as tms_now from dual);

The microsecond of the day:

  select to_number(to_char(tms_now, 'sssss') || to_char(tms_now, 'ff')) day_microsecond
    from (select systimestamp as tms_now from dual);

Upvotes: 0

AhmedAboElMagd
AhmedAboElMagd

Reputation: 1

SELECT 
TRUNC((L.OUT_TIME-L.IN_TIME)*24)           ||':'||
TRUNC((L.OUT_TIME-L.IN_TIME)*24*60)        ||':'||
ROUND(CASE WHEN ((L.OUT_TIME-L.IN_TIME)*24*60*60)>60 THEN ((L.OUT_TIME-L.IN_TIME)*24*60*60)-60 ELSE ((L.OUT_TIME-L.IN_TIME)*24*60*60) END ,5) Elapsed
FROM XYZ_TABLE

Upvotes: 0

user330315
user330315

Reputation:

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;

Upvotes: 11

Related Questions