2

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

0xdb
  • 3,539
  • 1
  • 21
  • 37
Jack
  • 197
  • 1
  • 2
  • 11

2 Answers2

4

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

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
3

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.

Chris Saxon
  • 9,105
  • 1
  • 26
  • 42