Julian Calendar Conversion using INDB

[GayatriPanigrahi]

I working with huge records of data and using INDB tools. I want to convert Julian date to Standard date format which I have achieved using Formula Tool but facing challenge to achieve same in Formula IN-DB tool. It will be really helpful if somebody suggest solution :

Standard Formula Tool

Year : IF Left([JD], 1) == '1' THEN '20' ELSE '19' ENDIF
Date : ToString(ToNumber([StartYear] + Substring([JD],1,2))-1)+'-12-31'
FinalDate : DateTimeAdd([StartDate],ToNumber(Substring([JD],3,3)),'days')

[Ladarthure]

Hi @GayatriPanigrahi,

could you share the in-db connection you are using, is it oracle, mysql, something else?

[GayatriPanigrahi]

I am using Oracle database and connecting to database using Connect In-DB

*Thanks and Regards*
*Gayatri Panigrahi*

[MarqueeCrew]

Stack overflow gave me this answer:

cheers,

mark

SELECT 
TO_DATE(1900+(DATE/1000),1,1)+TO_NUMBER(SUBSTR(TO_CHAR(DATE),4))-1  FROM DUAL;

[GayatriPanigrahi]

I am using SQL developer for querying database. I have tried few more
logics including below mentioned, I have achieved solution at database
level and in standard formula tool. But receiving syntax error while
implementing same logic in indb Formula tool.

While creating calculation in Indb formula tool I have called column name
in double quote and string value in single quote.

[GayatriPanigrahi · Accepted solution]

I wrote below code and have achieved standard date format using formula-indb tool

to_char(to_date(to_char(1900 + floor("JulianDate" / 1000)),'YYYY') + mod("JulianDate",1000) -1,'YYYY-MM-DD')