Q: How to retrieve date with time(hours,minutes,seconds) from a 19 digit number in Oracle

Question

I want to get date and time from a 19 digit number in Oracle and could not find any solution for this big numbers. The time should be in the format HH24:MI:SS. I am able to retrieve the date but unable to get the time.

**Example Numbers to get the date and time:

1190205161950369000 , 1190206092859146000 , 1181230172734928000 , 1181108121251823000

For this numbers above, I added them an addition value "19000000000000000000" and substring first 8 digits from the total value so that the first 8 digits give me the date(yyyymmdd).

For Example :

SUBSTR(1190205161950369000+19000000000000000000,0,8) = 20190205

Hence, the date is 05-02-2019(DD-MM-YYYY). But to get the time, even if I increase the substring length from 8 to 14 or 18 still I only get date and not time.

SQL Query Used :

Select to_date(SUBSTR(1190205161950369000+19000000000000000000,0,8),
    'YYYYMMDD HH24:MI:SS') as truedate
from dual;

Result:

TRUEDATE
---------
05-02-2019

I have also tried this but does not work :

Select to_date(SUBSTR(1190205161950369000+19000000000000000000,0,14),
    'YYYYMMDD HH24:MI:SS.SSSSS') as truedate
from dual;

Gordon Linoff · Answer 1 · 2019-02-06T12:20:41.293

2

First, to convert the value, this seems to work:

select to_date(substr(to_char(1190205161950369000 + 19000000000000000000), 1, 14), 'YYYYMMDDHH24MISS')
from dual

By default dates don't show the times. You can convert to a string or you can convert to a timestamp instead:

select to_timestamp(substr(to_char(1190205161950369000 + 19000000000000000000), 1, 14), 'YYYYMMDDHH24MISS')
from dual;

You can see that the time component is there by converting the date to a timestamp:

select to_timestamp(to_date(substr(to_char(1190205161950369000 + 19000000000000000000), 1, 14), 'YYYYMMDDHH24MISS'))
from dual

edited Feb 06 '19 at 12:20

answered Feb 06 '19 at 12:13

Gordon Linoff

1,242,037
58
646
786

Thanks. The second query converting it to timestamp works but the seconds contain too many fractions. When I run this query the output is : 05-02-19 16:19:50,000000000 – Dhanesh Agrawal Feb 06 '19 at 12:19
@DhaneshAgrawal . . . The time is there, you just can't see it. – Gordon Linoff Feb 06 '19 at 12:20

Salman A · Answer 2 · 2019-02-06T12:53:53.613

2

Seems like the time portion contains fractional seconds consisting of six digits. You can convert it like so:

WITH tests AS (
   SELECT 1190205161950369000 AS n FROM DUAL UNION
   SELECT 1190206092859146000 FROM DUAL UNION 
   SELECT 1181230172734928000 FROM DUAL UNION 
   SELECT 1181108121251823000 FROM DUAL
)
SELECT n, TO_TIMESTAMP(TO_CHAR(n + 19000000000000000000), 'YYYYMMDDHH24MISSFF')
FROM tests

edited Feb 06 '19 at 12:53

answered Feb 06 '19 at 12:22

Salman A

262,204
82
430
521

Thanks Salman. Yes it seems to have microseconds which I do not want to show :( – Dhanesh Agrawal Feb 06 '19 at 12:29
1

You can use Gordon's answer (1st one). The date will contain hour, minute and seconds. If necessary call the to_char again to format the date as desired (`to_char(to_date(substr(to_char..., 'YYYY-MM-DD HH24:MI:SS')`) – Salman A Feb 06 '19 at 12:53

Q: How to retrieve date with time(hours,minutes,seconds) from a 19 digit number in Oracle

2 Answers2