In-Database Julian date conversion

[jeffjose33]

I am trying to convert a date field I am getting from a database from julian format into a standard yyyy-mm-dd format (or any format that will let me parse the year and month, I need those values , e.g. 10 for month and 2021 for year for Oct 2021).

I currently have the date field coming in as Int32 with a size of 4 from the database:

I'm using the following formula to convert from Julian format date, but I am getting a DatawrapOCI error:

Error: Formula In-DB (80): DataWrapOCI: Unable to prepare the query: "WITH "Tool28_0b89" AS (select *
from PRODDTA.F55CSC
order by PRODDTA.F55CSC.CSDL01, PRODDTA.F55CSC.CSEFTE desc), "Tool29_4339" AS (SELECT "CSDL01" AS "CSCID_Child", "CSE74RT01" AS "Description_Child", "CSDL02" AS "CSCID_Parent", "CSE74RT02" AS "Description_Parent", "CSCRR" AS "Ownership_Percentage", "CSDL04" AS "JDE_Company_Number", "CSEFTE" AS "Ending_Date", "CSN001", "CSUPMJ", "CSATCD01DS" AS "SEC_Consolidation_Method", "CSATCD02DS" AS "Tax_Consolidation_Method" FROM "Tool28_0b89") SELECT "CSCID_Child", "Description_Child", "CSCID_Parent", "Description_Parent", "Ownership_Percentage", "JDE_Company_Number", (Dateadd(STR(FLOOR("Ending_Date"/1000)+1900)+'-01-01',MOD("Ending_Date",1000)-1,'days')) AS "Ending_Date", "CSN001", "CSUPMJ", "SEC_Consolidation_Method", "Tax_Consolidation_Method" FROM "Tool29_4339"" Error: ORA-00904: "DATEADD": invalid identifier

I am copying this formula from a normal (non-database) formula tool and it is working perfectly fine for me in that tool. I just prefer to convert and sort by date using In-database tool to save workflow runtime.

Here is some sample Julian date data (left) and the desired result format (right):

Thanks in advance 

[DanM]

@jeffjose33 

Taking an expression from the Designer Formula tool to the In-db Tool normally doesn't work. The in-db requires a SQL statement which the normal Formula tool does not create that. Here's a possible SQL statement that may work https://stackoverflow.com/questions/33550260/convert-julian-date-to-yyyy-mm-dd