Hi, I am trying to convert a column containing Julian dates to calendar dates using the replace tool.
Expression which generates the error:
DATETIMEADD(TOSTRING(FLOOR([Ref Date]/1000)+1900)+"-01-01",MOD([Ref Date],1000)-1,"days")
[Ref Date]=ToNumber(IF [SDPPDJ]=Null() THEN [SDIVD] ELSE IF [SDIVD]=Null() THEN 0 ELSE [SDPPDJ] ENDIF ENDIF)
Basically I have 2 columns coming from JD Edwards Tables via ODBC connection: SDIVD & SDPPDJ. They both contain dates in julian format (e.g. 118015). When combining them together - ToNumber(IF [SDPPDJ]=Null() THEN [SDIVD] ELSE IF [SDIVD]=Null() THEN 0 ELSE [SDPPDJ] ENDIF ENDIF) - there is no issue, but when I am trying to convert them to a calendar date I receive the error message above.
I replaced the [Ref Date] with [SDIVD] and then the conversion works.
Solved! Go to Solution.
Somewhere in your incoming [Ref Date] data, the column contains or has been set to a STRING data type. When [Ref Date] is numeric, your code works. While you can convert (via a SELECT) the field to an INT64 (likely INT32 is big enough), any non-numeric values will convert to 0.
Please check this out and see if it solves the issue.
Cheers,
Mark
After reading your reply I noticed the Data Type drop down for the Ref Date filed, which defaulted to String. After changing this to Int32 the flow now runs smoothly.
Thank you!