Parse Error at Char(0):Formula Tried to apply numeric operator to string value

Question

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.

MarqueeCrew · Accepted Answer

@Alex_A,

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