CODEWITHSUNDEEP
oracle-databasetimestampjulian-date
Jack
Jack

Reputation: 197

Oracle query for Convert from Julian value to Date with Time

I have to convert from Julian value to Regular Date with time.

Select to_char(to_date(2455198, 'J'), 'DD-MM-YYYY HH:mm:ss') from dual;

This query is working and the result is 01-01-2010 12:00:00

Select to_char(to_date(2455197.75001, 'J'), 'DD-MM-YYYY HH:mm:ss') from dual;

This above query is not working and got this error:

ORA:01830: date format picture ends before converting entire input string

Julian value: 2455197.75001 and equivalent date value is: 1-January-2010 06:00:01

Upvotes: 2

Views: 1248

Answers (2)

Alex Poole
Alex Poole

Reputation: 191415

As @Chris said, J needs an integer so you can use floor or trunc to get that, and use the fractional part as a number of partial days (and add another half a day, as @Chris noted in a comment, to account for Julian days starting at noon):

Select to_char(
    to_date(floor(2455197.75001), 'J')
      + mod(2455197.75001, 1)
      + 0.5,
  'DD-MM-YYYY HH24:mi:ss')
from dual;

which gets:

01-01-2010 06:00:01

Note that I changed HH to HH24, and the second mm to mi for minutes.

db<>fiddle

Upvotes: 5

Chris Saxon
Chris Saxon

Reputation: 9865

You can't use fractions with Julian values. As the docs state:

Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.

Upvotes: 3

Related Questions